Bulk insert的用法

  1 /******* 导出到excel */
  2 EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:/temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""' 
  3 
  4 /*********** 导入Excel */
  5 SELECT * 
  6 FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 
  7 'Data Source="c:/test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions 
  8 
  9 /*动态文件名 
 10 declare @fn varchar(20),@s varchar(1000) 
 11 set @fn = 'c:/test.xls' 
 12 set @s ='''Microsoft.Jet.OLEDB.4.0'', 
 13 ''Data Source="'+@fn+'";User ID=Admin;Password=;Extended properties=Excel 5.0''' 
 14 set @s = 'SELECT * FROM OpenDataSource ('+@s+')...sheet1$' 
 15 exec(@s) 
 16 */ 
 17 
 18 SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+'　' 转换后的别名 
 19 FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 
 20 'Data Source="c:/test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions 
 21 
 22 /********************** EXCEL导到远程SQL */
 23 insert OPENDATASOURCE( 
 24 'SQLOLEDB', 
 25 'Data Source=远程ip;User ID=sa;Password=密码' 
 26 ).库名.dbo.表名 (列名1,列名2) 
 27 SELECT 列名1,列名2 
 28 FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 
 29 'Data Source="c:/test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions 
 30 
 31 
 32 /** 导入文本文件 */
 33 EXEC master..xp_cmdshell 'bcp dbname..tablename in c:/DT.txt -c -Sservername -Usa -Ppassword' 
 34 
 35 /** 导出文本文件 */
 36 EXEC master..xp_cmdshell 'bcp dbname..tablename out c:/DT.txt -c -Sservername -Usa -Ppassword' 
 37 或 
 38 EXEC master..xp_cmdshell 'bcp "Select * from dbname..tablename" queryout c:/DT.txt -c -Sservername -Usa -Ppassword' 
 39 
 40 /**导出到TXT文本，用逗号分开*/ 
 41 exec master..xp_cmdshell 'bcp "库名..表名" out "d:/tt.txt" -c -t ,-U sa -P password' 
 42 
 43 
 44 BULK INSERT 库名..表名 
 45 FROM 'c:/test.txt' 
 46 WITH ( 
 47 FIELDTERMINATOR = ';', 
 48 ROWTERMINATOR = '/n' 
 49 ) 
 50 
 51 
 52 --/* dBase IV文件 
 53 select * from 
 54 OPENROWSET('MICROSOFT.JET.OLEDB.4.0' 
 55 ,'dBase IV;HDR=NO;IMEX=2;DATABASE=C:/','select * from [客户资料4.dbf]') 
 56 --*/ 
 57 
 58 --/* dBase III文件 
 59 select * from 
 60 OPENROWSET('MICROSOFT.JET.OLEDB.4.0' 
 61 ,'dBase III;HDR=NO;IMEX=2;DATABASE=C:/','select * from [客户资料3.dbf]') 
 62 --*/ 
 63 
 64 --/* FoxPro 数据库 
 65 select * from openrowset('MSDASQL', 
 66 'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:/', 
 67 'select * from [aa.DBF]') 
 68 --*/ 
 69 
 70 /**************导入DBF文件****************/ 
 71 select * from openrowset('MSDASQL', 
 72 'Driver=Microsoft Visual FoxPro Driver; 
 73 SourceDB=e:/VFP98/data; 
 74 SourceType=DBF', 
 75 'select * from customer where country != "USA" order by country') 
 76 go 
 77 /***************** 导出到DBF ************** 
 78 如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句 */
 79 
 80 insert into openrowset('MSDASQL', 
 81 'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:/', 
 82 'select * from [aa.DBF]') 
 83 select * from 表 
 84 
 85 /*说明: 
 86 SourceDB=c:/ 指定foxpro表所在的文件夹 
 87 aa.DBF 指定foxpro表的文件名. */
 88 
 89 
 90 /*************导出到Access********************/ 
 91 insert into openrowset('Microsoft.Jet.OLEDB.4.0', 
 92 'x:/A.mdb';'admin';'',A表) select * from 数据库名..B表 
 93 
 94 /*************导入Access********************/ 
 95 insert into B表 selet * from openrowset('Microsoft.Jet.OLEDB.4.0', 
 96 'x:/A.mdb';'admin';'',A表) 
 97 
 98 /*文件名为参数*/  
 99 declare @fname varchar(20) 
100 set @fname = 'd:/test.mdb' 
101 exec('SELECT a.* FROM opendatasource(''Microsoft.Jet.OLEDB.4.0'', 
102 '''+@fname+''';''admin'';'''', topics) as a ') 
103 
104 SELECT * 
105 FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 
106 'Data Source="f:/northwind.mdb";Jet OLEDB:Database Password=123;User ID=Admin;Password=;')...产品