/******* 导出到excel EXEC master..xp_cmdshell \’bcp SettleDB.dbo.shanghu out c:\\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""\’ /*********** 导入Excel Select * FROM OpenDataSource( \’Microsoft.Jet.OLEDB.4.0\’, \’Data Source="c:\\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0\’)…xactions /*动态文件名 declare @fn varchar(20),@s varchar(1000) set @fn = \’c:\\test.xls\’ set @s =\’\’\’Microsoft.Jet.OLEDB.4.0\’\’, \’\’Data Source="\’+@fn+\’";User ID=Admin;Password=;Extended properties=Excel 5.0\’\’\’ set @s = \’Select * FROM OpenDataSource (\’+@s+\’)…sheet1$\’ exec(@s) */ Select cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+\’ \’ 转换后的别名 FROM OpenDataSource( \’Microsoft.Jet.OLEDB.4.0\’, \’Data Source="c:\\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0\’)…xactions /********************** EXCEL导到远程SQL insert OPENDATASOURCE( \’SQLOLEDB\’, \’Data Source=远程ip;User ID=sa;Password=密码\’ ).库名.dbo.表名 (列名1,列名2) Select 列名1,列名2 FROM OpenDataSource( \’Microsoft.Jet.OLEDB.4.0\’, \’Data Source="c:\\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0\’)…xactions /** 导入文本文件 EXEC master..xp_cmdshell \’bcp dbname..tablename in c:\\DT.txt -c -Sservername -Usa -Ppassword\’ /** 导出文本文件 EXEC master..xp_cmdshell \’bcp dbname..tablename out c:\\DT.txt -c -Sservername -Usa -Ppassword\’ 或 EXEC master..xp_cmdshell \’bcp "Select * from dbname..tablename" queryout c:\\DT.txt -c – Sservername -Usa -Ppassword\’ 导出到TXT文本，用逗号分开 exec master..xp_cmdshell \’bcp "库名..表名" out "d:\\tt.txt" -c -t ,-U sa -P password\’ BULK Insert 库名..表名 FROM \’c:\\test.txt\’ WITH ( FIELDTERMINATOR = \’;\’, ROWTERMINATOR = \’\\n\’ ) –/* dBase IV文件 select * from OPENROWSET(\’MICROSOFT.JET.OLEDB.4.0\’ ,\’dBase IV;HDR=NO;IMEX=2;DATABASE=C:\\\’,\’select * from [客户资料4.dbf]\’) –*/ –/* dBase III文件 select * from OPENROWSET(\’MICROSOFT.JET.OLEDB.4.0\’ ,\’dBase III;HDR=NO;IMEX=2;DATABASE=C:\\\’,\’select * from [客户资料3.dbf]\’) –*/ –/* FoxPro 数据库 select * from openrowset(\’MSDASQL\’, \’Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\\\’, \’select * from [aa.DBF]\’) –*/ /**************导入DBF文件****************/ select * from openrowset(\’MSDASQL\’, \’Driver=Microsoft Visual FoxPro Driver; SourceDB=e:\\VFP98\\data; SourceType=DBF\’, \’select * from customer where country != "USA" order by country\’) go /***************** 导出到DBF ***************/ 如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句 insert into openrowset(\’MSDASQL\’, \’Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\\\’, \’select * from [aa.DBF]\’) select * from 表
上一页12 3 下一页
