--
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外,均不支持非本机数据导入
-- 导出到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外,均不支持非本机数据导入