SQL语句导入导出大全

SQL语句导入导出大全，包括excel,access- -                                        /*******  导出到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  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  /** 导入文本文件  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 表  说明:  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表 selet * from openrowset(’Microsoft.Jet.OLEDB.4.0’,     ’x:/A.mdb’;’admin’;’’,A表)  *********************  导入 xml　文件  DECLARE @idoc int  DECLARE @doc varchar(1000)  --sample XML document  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 @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 @idoc  /********************导整个数据库*********************************************/  用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  /**********************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将文件导入导出到数据库的存储过程:  /*--bcp-二进制文件的导入导出   支持image,text,ntext字段的导入/导出   image适合于二进制文件;text,ntext适合于文本数据文件   注意:导入时,将覆盖满足条件的所有行    导出时,将把所有满足条件的行也出到指定文件中   此存储过程仅用bcp实现  邹建 2003.08-----------------*/  /*--调用示例  --数据导出   exec p_binaryIO ’zj’,’’,’’,’acc_演示数据..tb’,’img’,’c:/zj1.dat’  --数据导出   exec p_binaryIO ’zj’,’’,’’,’acc_演示数据..tb’,’img’,’c:/zj1.dat’,’’,0  --*/  if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[p_binaryIO]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)  drop procedure [dbo].[p_binaryIO]  GO  Create proc p_binaryIO  @servename varchar (30),--服务器名称  @username varchar (30), --用户名  @password varchar (30), --密码  @tbname varchar (500),  --数据库..表名  @fdname varchar (30),  --字段名  @fname varchar (1000), --目录+文件名,处理过程中要使用/覆盖:@filename+.bak  @tj varchar (1000)=’’,  --处理条件.对于数据导入,如果条件中包含@fdname,请指定表名前缀  @isout bit=1   --1导出((默认),0导入  AS  declare @fname_in varchar(1000) --bcp处理应答文件名   ,@fsize varchar(20)   --要处理的文件的大小   ,@m_tbname varchar(50)  --临时表名   ,@sql varchar(8000)  --则取得导入文件的大小  if @isout=1   set @fsize=’0’  else  begin   create table #tb(可选名 varchar(20),大小 int    ,创建日期 varchar(10),创建时间 varchar(20)    ,上次写操作日期 varchar(10),上次写操作时间 varchar(20)    ,上次访问日期 varchar(10),上次访问时间 varchar(20),特性 int)   insert into #tb   exec master..xp_getfiledetails @fname   select @fsize=大小 from #tb   drop table #tb   if @fsize is null   begin    print ’文件未找到’    return   end  end  --生成数据处理应答文件  set @m_tbname=’[##temp’+cast(newid() as varchar(40))+’]’  set @sql=’select * into ’+@m_tbname+’ from(   select null as 类型   union all select 0 as 前缀   union all select ’+@fsize+’ as 长度   union all select null as 结束   union all select null as 格式   ) a’  exec(@sql)  select @fname_in=@fname+’_temp’   ,@sql=’bcp "’+@m_tbname+’" out "’+@fname_in   +’" /S"’+@servename   +case when isnull(@username,’’)=’’ then ’’    else ’" /U"’+@username end   +’" /P"’+isnull(@password,’’)+’" /c’  exec master..xp_cmdshell @sql  --删除临时表  set @sql=’drop table ’+@m_tbname  exec(@sql)  if @isout=1  begin   set @sql=’bcp "select top 1 ’+@fdname+’ from ’    +@tbname+case isnull(@tj,’’) when ’’ then ’’     else ’ where ’+@tj end    +’" queryout "’+@fname    +’" /S"’+@servename    +case when isnull(@username,’’)=’’ then ’’     else ’" /U"’+@username end    +’" /P"’+isnull(@password,’’)    +’" /i"’+@fname_in+’"’   exec master..xp_cmdshell @sql  end  else  begin   --为数据导入准备临时表   set @sql=’select top 0 ’+@fdname+’ into ’    +@m_tbname+’ from ’ +@tbname   exec(@sql)   --将数据导入到临时表   set @sql=’bcp "’+@m_tbname+’" in "’+@fname    +’" /S"’+@servename    +case when isnull(@username,’’)=’’ then ’’     else ’" /U"’+@username end    +’" /P"’+isnull(@password,’’)    +’" /i"’+@fname_in+’"’   exec master..xp_cmdshell @sql      --将数据导入到正式表中   set @sql=’update ’+@tbname    +’ set ’+@fdname+’=b.’+@fdname    +’ from ’+@tbname+’ a,’    +@m_tbname+’ b’    +case isnull(@tj,’’) when ’’ then ’’     else ’ where ’+@tj end   exec(@sql)   --删除数据处理临时表   set @sql=’drop table ’+@m_tbname  end  --删除数据处理应答文件  set @sql=’del ’+@fname_in  exec master..xp_cmdshell @sql  go  /** 导入文本文件  EXEC master..xp_cmdshell ’bcp "dbname..tablename" in c:/DT.txt -c -Sservername -Usa -Ppassword’  改为如下，不需引号  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’  此句需加引号      SQL SERVER 与ACCESS、EXCEL的数据转换 熟悉SQL SERVER 2000的数据库管理员都知道，其DTS可以进行数据的导入导出，其实，我们也可以使用Transact-SQL语句进行导入导出操作。在Transact-SQL语句中，我们主要使用OpenDataSource函数、OPENROWSET 函数，关于函数的详细说明，请参考SQL联机帮助。利用下述方法，可以十分容易地实现SQL SERVER、ACCESS、EXCEL数据转换，详细说明如下： 一、SQL SERVER 和ACCESS的数据导入导出 常规的数据导入导出： 使用DTS向导迁移你的Access数据到SQL Server，你可以使用这些步骤: 1在SQL SERVER企业管理器中的Tools（工具）菜单上，选择Data Transformation 2Services（数据转换服务），然后选择  czdImport Data（导入数据）。 3在Choose a Data Source（选择数据源）对话框中选择Microsoft Access as the Source，然后键入你的.mdb数据库(.mdb文件扩展名)的文件名或通过浏览寻找该文件。 4在Choose a Destination（选择目标）对话框中，选择Microsoft OLE　DB Prov ider for SQL　Server，选择数据库服务器，然后单击必要的验证方式。 5在Specify Table Copy（指定表格复制）或Query（查询）对话框中，单击Copy tables（复制表格）。 6在Select Source Tables（选择源表格）对话框中，单击Select All（全部选定）。下一步，完成。 Transact-SQL语句进行导入导出： 1.在SQL SERVER里查询access数据: -- ====================================================== SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:/DB.mdb";User ID=Admin;Password=')...表名 ------------------------------------------------------------------------------------------------- 2.将access导入SQL server -- ====================================================== 在SQL SERVER 里运行: SELECT * INTO newtable FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',       'Data Source="c:/DB.mdb";User ID=Admin;Password=' )...表名 ------------------------------------------------------------------------------------------------- 3.将SQL SERVER表里的数据插入到Access表中 -- ====================================================== 在SQL SERVER 里运行： insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',   'Data Source=" c:/DB.mdb";User ID=Admin;Password=')...表名 (列名1,列名2) select 列名1,列名2  from  sql表 实例： insert into  OPENROWSET('Microsoft.Jet.OLEDB.4.0',    'C:/db.mdb';'admin';'', Test) select id,name from Test INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:/trade.mdb'; 'admin'; '', 表名) SELECT * FROM sqltablename ------------------------------------------------------------------------------------------------- 二、SQL SERVER 和EXCEL的数据导入导出 1、在SQL SERVER里查询Excel数据: -- ====================================================== SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:/book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] 下面是个查询的示例，它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。 SELECT * FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',   'Data Source="c:/Finance/account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions ------------------------------------------------------------------------------------------------- 2、将Excel的数据导入SQL server : -- ====================================================== SELECT * into newtable FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',   'Data Source="c:/book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] 实例: SELECT * into newtable FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',   'Data Source="c:/Finance/account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions ------------------------------------------------------------------------------------------------- 3、将SQL SERVER中查询到的数据导成一个Excel文件 -- ====================================================== T-SQL代码： EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:/Temp.xls -c -q -S"servername" -U"sa" -P""' 参数：S 是SQL服务器名；U是用户；P是密码 说明：还可以导出文本文件等多种格式 实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:/temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"' EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:/ authors.xls -c -Sservername -Usa -Ppassword' 在VB6中应用ADO导出EXCEL文件代码： Dim cn  As New ADODB.Connection cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;" cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:/DT.xls -c -Sservername -Usa -Ppassword'" ------------------------------------------------------------------------------------------------ 4、在SQL SERVER里往Excel插入数据: -- ====================================================== insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:/Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3) T-SQL代码： INSERT INTO  OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',  'Extended Properties=Excel 8.0;Data source=C:/training/inventur.xls')...[Filiale1$]  (bestand, produkt) VALUES (20, 'Test')  ------------------------------------------------------------------------------------------------- 总结：利用以上语句，我们可以方便地将SQL SERVER、ACCESS和EXCEL电子表格软件中的数据进行转换，为我们提供了极大方便！