ããå¨æ¶åå°SQL Serverç¼ç¨ææ¯ç®¡çæ¶ä¸å®ä¼ç¨å°æ°æ®ç导å
¥ä¸å¯¼åº, 导å
¥å¯¼åºçæ¹æ³æå¤ç§,ç»åæå¨å项ç®æ¶çç»ååä¸ä¸æ±æ»:
ãã1. SQL Server导å
¥å¯¼åºå导,è¿ç§æ¹å¼æ¯ææ¹ä¾¿ç.
ãã导å
¥å导,微软æä¾äºå¤ç§æ°æ®æºé©±å¨,å
æ¬SQL Server Native Cliant, OLE DB For Oracle,Flat File Source,Access,Excel,XMLç,åºæ¬ä¸å¯ä»¥æ»¡è¶³ç³»ç»å¼åçéæ±.
ããåæ ·å¯¼åºå导ä¹æåæ ·å¤çç®çæºé©±å¨,å¯ä»¥ææ°æ®å¯¼å
¥å°ä¸åçç®çæº.
ãã对æ°æ®åºç®¡ç人åæ¥è¯´è¿ç§æ¹å¼ç®å容ææä½,导å
¥æ¶SQL Serverä¹ä¼å¸®ä½ 建ç«ç¸åç»æçTable.
ãã2. ç¨.NETç代ç å®ç°(æ¯å¦æä¸ä¸ªtxtææ¯excelçæ¡£æ¡,å°è¯»åå°DBä¸)
ãã2.1 æ为常è§çå°±æ¯å¾ªç¯è¯»åtxtçå
容,ç¶åä¸æ¡ä¸æ¡çå¡å
¥å°Tableä¸.è¿éä¸åèµè¿°.
ãã2.2 éåæ´ä½è¯»å,使ç¨OLEDB驱å¨.
ãã代ç å¦ä¸:
ãã
ããå¤å¶ä»£ç
ãã代ç
ããstring strOLEDBConnect = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\1\;Extended Properties='text;HDR=Yes;FMT=Delimited'";
ããOleDbConnection conn = new OleDbConnection(strOLEDBConnect);
ããconn.Open();
ããSQLstmt = "select * from 1.txt";//读å.txtä¸çæ°æ®
ããDataTable dt=new DataTable();
ããOleDbDataAdapter da = new OleDbDataAdapter(SQLstmt, conn);
ããda.Fill(dt);//å¨DataSetçæå®èå´ä¸æ·»å æå·æ°è¡ä»¥å¹é
使ç¨DataSetãDataTable åIDataReader å称çæ°æ®æºä¸çè¡ã
ããif(dt.Rows.Count>0)
ããforeach(DataRow dr in dt.Rows)
ãã{
ããSQLstmt = "insert into MyTable values('" + dr..."
ããå¤å¶ä»£ç
ãã
ãã
ãã3.BCP,å¯ä»¥ç¨ä½å¤§å®¹éçæ°æ®å¯¼å
¥å¯¼åº,ä¹å¯ä»¥é
åæ¥ä½¿ç¨.
ããè¯æ³:
ããå¤å¶ä»£ç
ãã代ç
ããbcp {[[database_name.][schema].]{table_name | view_name} | "query"}
ãã{in | out | queryout | format} data_file
ãã[-mmax_errors] [-fformat_file] [-x] [-eerr_file]
ãã[-Ffirst_row] [-Llast_row] [-bbatch_size]
ãã[-ddatabase_name] [-n] [-c] [-N] [-w] [-V (70 | 80 | 90 )]
ãã[-q] [-C { ACP | OEM | RAW | code_page } ] [-tfield_term]
ãã[-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size]
ãã[-S [server_name[\instance_name]]] [-Ulogin_id] [-Ppassword]
ãã[-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]
ããå¤å¶ä»£ç
ãã
ãã请注ææ°æ®å¯¼å
¥å¯¼åºçæ¹ååæ°:in,out,queryout
ããå¦:
ãã
ãã
ãã
ããå¦:
ãã
ãã
ãã4.BULK INSERT. T-SQLçå½ä»¤,å
许ç´æ¥å¯¼å
¥æ°æ®
ããè¯æ³:
ãã
ããå¤å¶ä»£ç
ããBULK INSERT
ãã[ database_name. [ schema_name ] . | schema_name. ] [ table_name | view_name ]
ããFROM 'data_file'
ãã[ WITH
ãã(
ãã[ [ , ] BATCHSIZE =batch_size ]
ãã[ [ , ] CHECK_CONSTRAINTS ]
ãã[ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
ãã[ [ , ] DATAFILETYPE =
ãã{ 'char' | 'native'| 'widechar' | 'widenative' } ]
ãã[ [ , ] FIELDTERMINATOR = 'field_terminator' ]
ãã[ [ , ] FIRSTROW = first_row ]
ãã[ [ , ] FIRE_TRIGGERS ]
ãã[ [ , ] FORMATFILE ='format_file_path' ]
ãã[ [ , ] KEEPIDENTITY ]
ãã[ [ , ] KEEPNULLS ]
ãã[ [ , ] KILOBYTES_PER_BATCH =kilobytes_per_batch ]
ãã[ [ , ] LASTROW =last_row ]
ãã[ [ , ] MAXERRORS =max_errors ]
ãã[ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
ãã[ [ , ] ROWS_PER_BATCH =rows_per_batch ]
ãã[ [ , ] ROWTERMINATOR ='row_terminator' ]
ãã[ [ , ] TABLOCK ]
ãã[ [ , ] ERRORFILE ='file_name' ]
ãã)]
ããå¤å¶ä»£ç
ãã
ãã
ããéè¦åæ°:
ãã
ããFIELDTERMINATOR,å段åé符
ããFIRSTROW:第ä¸ä¸ªæ°æ®è¡
ããROWTERMINATOR:è¡ç»ç»ç¬¦
ããå¦:
ããBULK INSERT dbo.ImportTest
ããFROM 'C:\ImportData.txt'
ããWITH ( FIELDTERMINATOR =',', FIRSTROW = 2 )
ãã
ãã5. OPENROWSETä¹æ¯T-SQLçå½ä»¤,å
å«æDBè¿æ¥çä¿¡æ¯åå
¶å®å¯¼å
¥æ¹æ³ä¸åçæ¯,OPENROWSETå¯ä»¥ä½ä¸ºä¸ä¸ªç®æ 表åä¸INSERT,UPDATE,DELETEæä½.
ããè¯æ³:
ãã
ããå¤å¶ä»£ç
ããOPENROWSET
ãã( { 'provider_name', { 'datasource';'user_id';'password'
ãã| 'provider_string' }
ãã, { [ catalog. ] [ schema. ] object
ãã| 'query'
ãã}
ãã| BULK 'data_file',
ãã{ FORMATFILE ='format_file_path' [ <bulk_options> ]
ãã| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
ãã} )<bulk_options> ::=
ãã[ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
ãã[ , ERRORFILE ='file_name' ]
ãã[ , FIRSTROW = first_row ]
ãã[ , LASTROW = last_row ]
ãã[ , MAXERRORS = maximum_errors ]
ãã[ , ROWS_PER_BATCH =rows_per_batch ]
ããå¤å¶ä»£ç
ãã
ãã
ãã
ããå¦:
ããINSERT INTO dbo.ImportTest
ããSELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
ãã'Excel 8.0;Database=C:\ImportData.xls', 'SELECT * FROM [Sheet1$]') WHERE A1 IS NOT NULL
ãã
ãã
ãã6.OPENDATASOURCE
ããè¯æ³:
ããOPENDATASOURCE ( provider_name,init_string )
ããå¦:
ããINSERT INTO dbo.ImportTest
ããSELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
ãã'Data Source=C:\ImportData.xls;Extended Properties=Excel 8.0')...[Sheet1$]
ãã
ãã7.OPENQUERY.æ¯å¨linked serverçåºç¡ä¸æ§è¡çæ¥è¯¢.æ以æ§è¡ä¹åå¿
é¡»å
建ç«å¥½link server.OPENQUERYçç»æéå¯ä»¥ä½ä¸ºä¸ä¸ªtableåä¸DMLçæä½.
ãã
ããè¯æ³:
ããOPENQUERY (linked_server ,'query')
ããå¦:
ããEXEC sp_addlinkedserver 'ImportData',
ãã'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
ãã'C:\ImportData.xls',
ããNULL,
ãã'Excel 8.0'
ããGO
ãã
ããINSERT INTO dbo.ImportTest
ããSELECT *
ããFROM OPENQUERY(ImportData, 'SELECT * FROM [Sheet1$]')
温馨提示:答案为网友推荐,仅供参考