-- 1.excel------------------------------- -- 导出到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 = '' ) -- 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 表 -- 说明: -- SourceDB=c:/ 指定foxpro表所在的文件夹 -- aa.DBF 指定foxpro表的文件名. /**/ /**/ /**/ /*************导出到Access********************/ insert into openrowset ( ' Microsoft.Jet.OLEDB.4.0 ' , ' x:/A.mdb '' ; '' admin '' ; ''' ,A表) select * from 数据库名..B表 /**/ /**/ /**/ /*************导入Access********************/ insert into B表 select * from openrowset ( ' Microsoft.Jet.OLEDB.4.0 ' , ' x:/A.mdb '' ; '' admin '' ; ''' ,A表) -- 文件名为参数 declare @fname varchar ( 20 ) set @fname = ' d:/test.mdb ' exec ( ' SELECT a.* FROM opendatasource( '''' Microsoft.Jet.OLEDB.4.0 '''' , '''''' +@fname+ '''''' ; '''' admin '''' ; '''''''' , topics) as a ' ) SELECT * FROM OpenDataSource ( ' Microsoft.Jet.OLEDB.4.0 ' , ' Data Source="f:orthwind.mdb";Jet OLEDB:Database Password=123;User ID=Admin;Password=; ' )产品$ -- ********************* 导入 xml 文件 DECLARE @idoc int DECLARE @doc varchar ( 1000 ) -- --sample XML document.BR> SET @doc =' -- < root> -- < Customer cid= "C1" name="Janine" city="Issaquah"> -- < Order oid="O1" date="1/20/1996" amount="3.5" /> -- < Order oid="O2" date="4/30/1997" amount="13.4"> Customer was very satisfied -- < /Order> -- < /Customer> -- < Customer cid="C2" name="Ursula" city="Oelde" > -- < Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue -- white red"> -- < Urgency> Important< /Urgency> -- Happy Customer. -- < /Order> -- < Order oid="O4" date="1/20/1996" amount="10000"/> -- < /Customer> -- < /root> -- Create an internal representation of the XML document. -- EXEC sp_xml_preparedocument.nbsp@idoc OUTPUT, @doc -- Execute a SELECT statement using OPENXML rowset provider. SELECT * FROM OPENXML ( @idoc , ' /root/Customer/Order ' , 1 ) WITH (oid char ( 5 ), amount float , comment ntext ' text() ' ) -- EXEC sp_xml_removedocument.nbsp@idoc /**/ /**/ /**/ /**********************Excel导到Txt****************************************/ -- 想用 -- select * into opendatasource() from opendatasource() -- 实现将一个Excel文件内容导入到一个文本文件 -- -- 假设Excel中有两列,第一列为姓名,第二列为很行帐号(16位) -- 且银行帐号导出到文本文件后分两部分,前8位和后8位分开。 -- -- 如果要用你上面的语句插入的话,文本文件必须存在,而且有一行:姓名,银行账号1,银行账号2 -- 然后就可以用下面的语句进行插入 -- 注意文件名和目录根据你的实际情况进行修改. insert into opendatasource ( ' MICROSOFT.JET.OLEDB.4.0 ' , ' Text;HDR=Yes;DATABASE=C:/ ' ) [ aa#txt ] -- ,aa#txt) -- */ select 姓名,银行账号1 = left (银行账号, 8 ),银行账号2 = right (银行账号, 8 ) from opendatasource ( ' MICROSOFT.JET.OLEDB.4.0 ' , ' Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:/a.xls ' -- ,Sheet1$) ) [ Sheet1$ ] -- 如果你想直接插入并生成文本文件,就要用bcp declare @sql varchar ( 8000 ), @tbname varchar ( 50 ) -- 首先将excel表内容导入到一个全局临时表 select @tbname = ' [##temp ' + cast ( newid () as varchar ( 40 )) + ' ] ' , @sql = ' select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8) into ' + @tbname + ' from opendatasource( '' MICROSOFT.JET.OLEDB.4.0 '' , '' Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:/a.xls '' )[Sheet1$] ' exec ( @sql ) -- 然后用bcp从全局临时表导出到文本文件 set @sql = ' bcp " ' + @tbname + ' " out "c:/aa.txt" /S"(local)" /P"" /c ' exec master..xp_cmdshell @sql -- 删除临时表 exec ( ' drop table ' + @tbname ) /**/ /**/ /**/ /********************导整个数据库*********************************************/ -- 用bcp实现的存储过程 -- 实现数据导入/导出的存储过程 -- 根据不同的参数,可以实现导入/导出整个数据库/单个表 -- 调用示例: -- 导出调用示例 -- --导出单个表 exec file2table ' zj ' , '' , '' , ' xzkh_sa..地区资料 ' , ' c:/zj.txt ' , 1 -- --导出整个数据库 exec file2table ' zj ' , '' , '' , ' xzkh_sa ' , ' C:/docman ' , 1 -- 导入调用示例 -- --导入单个表 exec file2table ' zj ' , '' , '' , ' xzkh_sa..地区资料 ' , ' c:/zj.txt ' , 0 -- --导入整个数据库 exec file2table ' zj ' , '' , '' , ' xzkh_sa ' , ' C:/docman ' , 0 if exists ( select 1 from sysobjects where name = ' File2Table ' and objectproperty (id, ' IsProcedure ' ) = 1 ) drop procedure File2Table go create procedure File2Table @servername varchar ( 200 ) -- 服务器名 , @username varchar ( 200 ) -- 用户名,如果用NT验证方式,则为空'' , @password varchar ( 200 ) -- 密码 , @tbname varchar ( 500 ) -- 数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表 , @filename varchar ( 1000 ) -- 导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt , @isout bit -- 1为导出,0为导入 as declare @sql varchar ( 8000 ) if @tbname like ' %.%.% ' -- 如果指定了表名,则直接导出单个表 begin set @sql = ' bcp ' + @tbname + case when @isout = 1 then ' out ' else ' in ' end + ' " ' + @filename + ' " /w ' + ' /S ' + @servername + case when isnull ( @username , '' ) = '' then '' else ' /U ' + @username end + ' /P ' + isnull ( @password , '' ) exec master..xp_cmdshell @sql end else begin -- 导出整个数据库,定义游标,取出所有的用户表 declare @m_tbname varchar ( 250 ) if right ( @filename , 1 ) < > ' / ' set @filename = @filename + ' / ' set @m_tbname = ' declare #tb cursor for select name from ' + @tbname + ' ..sysobjects where xtype= '' U ''' exec ( @m_tbname ) open #tb fetch next from #tb into @m_tbname while @@fetch_status = 0 begin set @sql = ' bcp ' + @tbname + ' .. ' + @m_tbname + case when @isout = 1 then ' out ' else ' in ' end + ' " ' + @filename + @m_tbname + ' .txt " /w ' + ' /S ' + @servername + case when isnull ( @username , '' ) = '' then '' else ' /U ' + @username end + ' /P ' + isnull ( @password , '' ) exec master..xp_cmdshell @sql fetch next from #tb into @m_tbname end close #tb deallocate #tb end go /**/ /**/ /**/ /************* Oracle **************/ EXEC sp_addlinkedserver ' OracleSvr ' , ' Oracle 7.3 ' , ' MSDAORA ' , ' ORCLDB ' GO delete from openquery (mailser, ' select * from yulin ' ) select * from openquery (mailser, ' select * from yulin ' ) update openquery (mailser, ' select * from yulin where id=15 ' ) set disorder = 555 ,catago = 888 insert into openquery (mailser, ' select disorder,catago from yulin ' ) values ( 333 , 777 ) -- 补充: -- -- 对于用bcp导出,是没有字段名的. -- -- 用openrowset导出,需要事先建好表. -- -- 用openrowset导入,除ACCESS及EXCEL外,均不支持非本机数据导入