MS SQL语句导入导出大全

SQL语句导入导出大全    
   
  /*******     导出到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’    
  此句需加引号