sql 查询结果 excel

EXEC master..xp_cmdshell 'bcp "select a.ID,a.Name,a.Sex,a.Class,b.Score from a left join b on a.ID = b.ID where b.Score > 80" queryout 导出文件路径 -c -q -S"数据库服务器名" -U"数据库用户名" -P"数据库用户密码"'

 /*===================================================================*/  
  --如果从SQL数据库中,导出数据到Excel,如果Excel文件已经存在,而且已经按照要接收的数据创建好表头,就可以简单的用:  
  insert   into   OPENROWSET('MICROSOFT.JET.OLEDB.4.0'  
  ,'Excel   5.0;HDR=YES;DATABASE=c:/test.xls',sheet1$)  
  select   *   from   表  
   
   
  --如果Excel文件不存在,也可以用BCP来导成类Excel的文件,注意大小写:  
  --导出表的情况  
  EXEC   master..xp_cmdshell   'bcp   数据库名.dbo.表名   out   "c:/test.xls"   /c   -/S"服务器名"   /U"用户名"   -P"密码"'  
   
  --导出查询的情况  
  EXEC   master..xp_cmdshell   'bcp   "SELECT   au_fname,   au_lname   FROM   pubs..authors   ORDER   BY   au_lname"   queryout   "c:/test.xls"   /c   -/S"服务器名"   /U"用户名"   -P"密码"'  
   
  /*--说明:  
  c:/test.xls     为导入/导出的Excel文件名.  
  sheet1$             为Excel文件的工作表名,一般要加上$才能正常使用.  
  --*/  
     
   
  要导出真正的Excel文件.就用下面的方法
 
 
/**//*--数据导出EXCEL
 
 导出表中的数据到Excel,包含字段名,文件为真正的Excel文件
 ,如果文件不存在,将自动创建文件
 ,如果表不存在,将自动创建表
 基于通用性考虑,仅支持导出标准数据类型
--邹建 2003.10--*/
 
/**//*--调用示例
 
 p_exporttb @tbname='地区资料',@path='c:',@fname='aa.xls'
--*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_exporttb]
GO
 
create proc p_exporttb
@tbname sysname,    --要导出的表名
@path nvarchar(1000),   --文件存放目录
@fname nvarchar(250)=''  --文件名,默认为表名
as
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)
 
--参数检测
if isnull(@fname,'')='' set @fname=@tbname+'.xls'
 
--检查文件是否已经存在
if right(@path,1)<>'' set @path=@path+''
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql
 
--数据库创建语句
set @sql=@path+@fname
if exists(select 1 from #tb where a=1)
 set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'
       +';CREATE_DB="    +';DATABASE='+@sql+'"'
 
 
--连接数据库
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err<>0 goto lberr
 
exec @err=sp_oamethod @obj,'open',null,@constr
if @err<>0 goto lberr
 
/*--如果覆盖已经存在的表,就加上下面的语句
--创建之前先删除表/如果存在的话
select @sql='drop table ['+@tbname+']'
exec @err=sp_oamethod @obj,'execute',@out out,@sql
--*/
 
--创建表的SQL
select @sql='',@fdlist=''
select @fdlist=@fdlist+',['+a.name+']'
 ,@sql=@sql+',['+a.name+'] '
  +case when b.name in('char','nchar','varchar','nvarchar') then
     'text('+cast(case when a.length>255 then 255 else a.length end as varchar)+')'
   when b.name in('tynyint','int','bigint','tinyint') then 'int'
   when b.name in('smalldatetime','datetime') then 'datetime'
   when b.name in('money','smallmoney') then 'money'
   else b.name end
FROM syscolumns a left join systypes b on a.xtype=b.xusertype
where b.name not in('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')
 and object_id(@tbname)=id
select @sql='create table ['+@tbname
 +']('+substring(@sql,2,8000)+')'
 ,@fdlist=substring(@fdlist,2,8000)
exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err<>0 goto lberr
 
exec @err=sp_oadestroy @obj
 
--导入数据
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 5.0;HDR=YES
   ;DATABASE='+@path+@fname+''',['+@tbname+'$])'
 
exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from '+@tbname)
 
return
 
lberr:
 exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
 select cast(@err as varbinary(4)) as 错误号
  ,@src as 错误源,@desc as 错误描述
 select @sql,@constr,@fdlist
go
--上面是导表的,下面是导查询语句的.
 
/*--数据导出EXCEL
 
 导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件
 ,如果文件不存在,将自动创建文件
 ,如果表不存在,将自动创建表
 基于通用性考虑,仅支持导出标准数据类型
--邹建 2003.10--*/
 
/*--调用示例
 
 p_exporttb @sqlstr='select * from 地区资料'
  ,@path='c:',@fname='aa.xls',@sheetname='地区资料'
--*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_exporttb]
GO
 
create proc p_exporttb
@sqlstr varchar(8000),   --查询语句,如果查询语句中使用了order by ,请加上top 100 percent
@path nvarchar(1000),   --文件存放目录
@fname nvarchar(250),   --文件名
@sheetname varchar(250)=''  --要创建的工作表名,默认为文件名
as 
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)
 
--参数检测
if isnull(@fname,'')='' set @fname='temp.xls'
if isnull(@sheetname,'')='' set @sheetname=replace(@fname,'.','#')
 
--检查文件是否已经存在
if right(@path,1)<>'' set @path=@path+''
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql
 
--数据库创建语句
set @sql=@path+@fname
if exists(select 1 from #tb where a=1)
 set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'
       +';CREATE_DB="    +';DATABASE='+@sql+'"'
 
--连接数据库
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err<>0 goto lberr
 
exec @err=sp_oamethod @obj,'open',null,@constr
if @err<>0 goto lberr
 
--创建表的SQL
declare @tbname sysname
set @tbname='##tmp_'+convert(varchar(38),newid())
set @sql='select * into ['+@tbname+'] from('+@sqlstr+') a'
exec(@sql)
 
select @sql='',@fdlist=''
select @fdlist=@fdlist+',['+a.name+']'
 ,@sql=@sql+',['+a.name+'] '
  +case when b.name in('char','nchar','varchar','nvarchar') then
     'text('+cast(case when a.length>255 then 255 else a.length end as varchar)+')'
   when b.name in('tynyint','int','bigint','tinyint') then 'int'
   when b.name in('smalldatetime','datetime') then 'datetime'
   when b.name in('money','smallmoney') then 'money'
   else b.name end
FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype
where b.name not in('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')
 and a.id=(select id from tempdb..sysobjects where name=@tbname)
select @sql='create table ['+@sheetname
 +']('+substring(@sql,2,8000)+')'
 ,@fdlist=substring(@fdlist,2,8000)
 
exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err<>0 goto lberr
 
exec @err=sp_oadestroy @obj
 
--导入数据
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 5.0;HDR=YES
   ;DATABASE='+@path+@fname+''',['+@sheetname+'$])'
 
exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']')
 
set @sql='drop table ['+@tbname+']'
exec(@sql)
return
 
lberr:
 exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
 select cast(@err as varbinary(4)) as 错误号
  ,@src as 错误源,@desc as 错误描述
 select @sql,@constr,@fdlist
go
 --下面是批量导入/导出文本文件的.
 
/**//*--实现数据导入/导出的存储过程
 
 可以实现导入/导出 整个数据库/指定表 到文本文件
 
邹建 2003.07-----------------*/
 
/**//*--调用示例
导出调用示例
--导出指定表,这里指定导出表:地区资料 和 基本信息,文件名前缀为:zj
exec file2table 'zj','','','d:','xzkh_sa','地区资料,基本信息'
--导出整个数据库到c:docman目录下,无文件前缀
exec file2table 'zj','','','d:txt','xzkh_sa'
 
导入调用示例
--导入指定表,这里指定导出表:地区资料 和 基本信息,文件名前缀为:zj
exec file2table 'zj','','','c:zj','xzkh_sa','地区资料,基本信息',0
--导入整个数据库从c:docman目录下,无文件前缀
exec file2table 'zj','','','C:docman','xzkh_sa','',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)  --密码
,@path varchar(1000)  --目录名+文件前缀,目录名必须以''结束,文件名自动用表名.txt
,@dbname varchar(500)  --数据库名
,@tbname varchar(8000)='' --表名列表,如果不指定,则表示所有用户表
,@isout bit=1    --1为导出(默认),0为导入
as
declare @sql varchar(8000),@sql1 varchar(8000)
declare @tbstation int,@filestation int
 
--初始化数据处理语句
select @sql='bcp "'+@dbname+'..'
 ,@tbstation=len(@sql)+1
 ,@sql=@sql
 +case when @isout=1 then '" out' else '" in' end
 +' "'+@path
 ,@filestation=len(@sql)+1
 ,@sql=@sql
 +'.txt" /c' +' /S"'+@servername
 +case when isnull(@username,'')='' then '' 
  else '" /U"'+@username end
 +'" /P"'+isnull(@password,'')+'"'
 
--数据导入/导出处理
--定义数据处理的游标
set @sql1='declare #tb cursor for select name from '
 +@dbname+'..sysobjects where xtype=''U'''
 +case when isnull(@tbname,'')='' then ''
  else ' and name in ('''+replace(@tbname,',',''',''')+''')' end
exec(@sql1)
open #tb
fetch next from #tb into @tbname
while @@fetch_status=0
begin
 select @sql1=stuff(@sql,@tbstation,0,@tbname)
  ,@sql1=stuff(@sql1,@filestation+len(@tbname),0,@tbname)
 exec master..xp_cmdshell @sql1,no_output
 fetch next from #tb into @tbname
end
close #tb
deallocate #tb 
go
 

 

 

/***************************************************************************************************/

參考  
   
   
  打開Excel的一個表  
  (1).   select   *   from   OPENROWSET('MICROSOFT.JET.OLEDB.4.0'  
  ,'Excel   5.0;HDR=YES;DATABASE=e:/test.xls',sheet1$)  
  (2).   select   *   into   表   from   OPENROWSET('MICROSOFT.JET.OLEDB.4.0'  
  ，'Excel   5.0;HDR=YES;DATABASE=e:/test1.xls'，sheet3$)  
  (3).   SELECT   *   FROM   OpenDataSource(   'Microsoft.Jet.OLEDB.4.0',  
      'Data   Source=e:/lp.xls;User   ID=admin;Password=;Extended   properties=Excel   5.0')...園區管理$  
  SELECT   a1,a2,a3,a4,a5  
  FROM   OpenDataSource(   'Microsoft.Jet.OLEDB.4.0',  
  'Data   Source="E:/data/datasource.xls";  
  User   ID=admin;Password=;Extended   properties="Excel   8.0;HDR=Yes;";Persist   Security   Info=False')...[Sheet1$]  
   
   
   
  二、導入與導出  
  1. 用BCP工具導入導出  
  (1)xls文件  
  EXEC   master..xp_cmdshell   'bcp   "SELECT   class_no，kind_no，cn_name   FROM   new_ks.dbo.kind   ORDER   BY   1，2"   queryout   "e:/test3.xls"   -c   -q   -S"sprogram"   -U"develop"   -P"12345"'  
   
  (2)/**   导入文本文件  
  --入SQLServer  
  select   *     into   你的表   from   OpenRowset('MSDASQL'，   'Driver={Microsoft   Text   Driver   (*.txt;   *.csv)};DefaultDir=c:/temp;'，'select   *   from   contact.txt')  
  --查詢導出  
  EXEC   master..xp_cmdshell   'bcp   "SELECT   class_no，kind_no，cn_name   FROM   new_ks.dbo.kind   ORDER   BY   1，2"   queryout   "e:/TX1.txt"   -c   -q   -S"sprogram"   -U"develop"   -P"12345"'  
  --直接導出  
  EXEC   master..xp_cmdshell   'bcp   new_ks.dbo.class   out   "e:/TX3.txt"   -c   -S"sprogram"   -U"develop"   -P"12345"'  
   
  EXEC   master..xp_cmdshell   'bcp   hdh.dbo.tabx   out   "e:/TX30.txt"   -c   -S"ks052"   -U"huang"   -P"huang"'  
   
   
   
  --直接導入  
  EXEC   master..xp_cmdshell   'bcp   "數據庫.dbo.數據表"   in   c:/DT.txt   -c   -S服務器名   -U用戶   -P密碼'  
  EXEC   master..xp_cmdshell   'bcp   hdh.dbo.kind   in   "e:/TX1.txt"   -c   -q   -S   -U   -P'  
  EXEC   master..xp_cmdshell   'bcp   hdh.dbo.kind   in   "e:/test3.xls"   -c   -q   -S   -U   -P'  
   
  --用BULK   INSERT導入  
  BULK   INSERT   hdh.dbo.kind  
  FROM   'e:/tx1.txt'  
  WITH   (  
          FIELDTERMINATOR   =   '/t'，  
          ROWTERMINATOR   =   '/n'  
  )  
  前提條件是表如kind要存在，而且字段與test.txt字段數目要一樣多  
  在查詢分析器中運行，而且是以sa用戶登錄  
   
  bulk   insert   tbyhhk   from   'c:/t.txt'   with(   formatfile='c:/bcp.txt')   
    

导入导出大全    
  *******     导出到excel  
  EXEC   master..xp_cmdshell   'bcp   SettleDB.dbo.shanghu   out   c:/temp1.xls   -c   -q   -S"GNETDATA/GNETDATA"   -U"sa"   -P""'  
     
  /***********     导入Excel  
  select   *   from   openrowset('MicroSoft.Jet.OleDB.4.0',  
                                                    'Excel   5.0;HDR=yes;Database=c:/test.xls',sheet1$)  
  --------------------------------------------------------  
  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表)  
     
     
     
   
     
  Top    
     
    回复人：   txlicenhe(马可)   (   )   信誉：167     2003-10-10   18:52:32Z     得分:0      
     
     
       
  *********************     导入   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  
     
     
     
   
  Top    
     
    回复人：   txlicenhe(马可)   (   )   信誉：167     2003-10-10   18:54:30Z     得分:0      
     
     
       
  邹健的  
  /********************导整个数据库*********************************************/  
     
  用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