SQL导入导出使用大全

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