t-sql导出EXCEL语句
?
/*=================== 導入/導出 Excel 的基本方法 ===================*/
從Excel文件中,導入數據到SQL數據庫中,很簡單,直接用下面的語句:
/*========================================================*/
?EXEC master..xp_cmdshell 'bcp " exec weberp.dbo.DailyCash_Excel @PayAccount=null,@StartDate=null,@EndDate=null,@TrackStation=null" queryout "C:\authors.xls" -c -S "(local)" -U "sa" -P "password"'
--如果接受數據導入的表已經存在
insert into 表 select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:test.xls',sheet1$)
--如果導入數據并生成表
select * into 表 from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:test.xls',sheet1$)
/*========================================================*/
--如果從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,如果文件不存在,將自動創建文件,如果表不存在,將自動創建表?
? 調用示例?
? ExportToExcel? @tbname='test',@path='c:\',@fname='test.xls'?
? --*/?
? if? exists? (select? *? from? dbo.sysobjects? where? id? =? object_id(N'[dbo].[ExportToExcel]')? and? OBJECTPROPERTY(id,? N'IsProcedure')? =? 1)?
? drop? procedure? [dbo].[ExportToExcel]?
? GO?
??
? create? proc? ExportToExcel?
? @Pac? , --要導出的表名,注意只能是表名/視圖名?
? @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=TT;READONLY=FALSE'?
?????? +';CREATE_DB='+@sql+';DBQ='+@sql?
? else?
? set? @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended? Properties="Excel? 8.0;HDR=YES'?
? +';DATABASE='+@sql+'"'?
??
? --連接數據庫?
?print @constr
? 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?
? 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)?
?? print @sql
? 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 8.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?
CREATE?? PROC?? SqlToExcel??
? (??
? @Path?????????????????? varchar(100),--文件存放路徑??
? @Fname???????????????? varchar(100),--文件名字??
? @SheetName???????? varchar(80),---工作表名字??
? @SqlStr?????????????? varchar(8000)--查詢語句,如果查詢語句中使用了order?????? by?????? ,請加上top?????? 100?????? percent,注意,如果導出表/視圖,用上面的存儲過程??????
? )??
? AS??
? SET?? NOCOUNT?? ON??
???
? declare???? @sql???????????????? varchar(8000)??
? declare???? @obj???????????????? int--OLE對象??
? declare???? @constr?????????? varchar(8000)??
? declare???? @err???????????????? int??
? declare???? @out???????????????? int??
? declare???? @fdlist?????????? varchar(8000)??
? declare???? @tbname?????????? sysname--臨時表??
? declare???? @Src???????????????? nvarchar(200)??
? declare???? @Desc?????????????? nvarchar(200)??
???
? set?? @tbname='##tmp_'+convert(varchar(38),newid())??
???
? exec('select?? *?? into?? ['+@tbname?? +']?? from?? '+'('+@sqlStr+')?? A')??
???
? select?? @fdlist?? =?? ''??
???
? set?? @sql=?? @path+@fname??
??? set?? @constr='DRIVER={Microsoft?? Excel?? Driver?? (*.xls)};DSN='''';READONLY=FALSE'??
??????????????? +';CREATE_DB="'+@sql+'";DBQ='+@sql??
???
? --生成Excel的列??
? set?? @sql?? =?? ''??
? select?? @sql?? =?? @sql+','+'['+a.name+']?? '+?? case?? when?? b.name?? like?? '%char'?? then?? case?? when?? a.length?? >255?? then?? 'memo'?? else?? 'text('+cast(a.length?? as?? varchar)+')'?? end??
??????????????????????????? when?? b.name?? like?? '%int'?? or?? b.name='bit'?? then?? 'int'??
??????????? when?? b.name?? like?? '%datetime'?? then?? 'datetime'??
????????????????? when?? b.name?? like?? '%money'?? then?? 'money'??
??????????????????????????????????????????????????????????????????????????????????????????? when?? b.name?? like?? '%text'?? then?? 'memo'??
??????????? else?? b.name??
??????????????? end,??????
? @fdlist?? =?? @fdlist+','+'['+a.name+']'????
? from?? tempdb..syscolumns?? a???? join?? tempdb..systypes?? b?? on?? a.xtype?? =?? b.xusertype????
? where?? b.name?? not?? in('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp')????
????????????? and?? id?? in(select?? id?? from?? tempdb..sysobjects?? where?? name?? =?? @tbname)?? order?? by?? colorder??
? if?? @@rowcount=0?? return??
???
? set?? @fdlist?? =?? substring(@fdlist,2,8000)??
???
? --連接數據庫??
? exec?? @err=sp_oacreate?? 'adodb.connection',@obj?? out??
? if?? @err?? <>?? 0???? goto?? lberror??
? exec?? @err=sp_oamethod?? @obj,'open',null,@constr??
? if?? @err?? <>?? 0???? goto?? lberror??
? --創建工作薄??
? select?? @sql='create?? table?? ['+@sheetname??
??? +']('+substring(@sql,2,8000)+')'??
???
? exec?? @err=sp_oamethod?? @obj,'execute',@out?? out,@sql--@sql為excute方法提供參數??
???
? if?? @err?? <>?? 0???? goto?? lberror??
???
? exec?? @err=sp_oadestroy?? @obj??
???
? --導入數據??
? set?? @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel?? 8.0;HDR=YES??
??????? ;DATABASE='+@path+@fname+''',['+@sheetname+'$])'??
? --print?? @sql??
? exec?? ('insert?? into?? '+@sql+'('+@fdlist+')?? select?? '+@fdlist+'?? from?? ['+@tbname+']')??
???
? exec('drop?? table?? ['+@tbname+']')??
? return??
???
???
? lberror:??
??? 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??
轉載于:https://www.cnblogs.com/BlogNetSpace/articles/1215068.html
總結
以上是生活随笔為你收集整理的t-sql导出EXCEL语句的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: Delphi编译指令说明
- 下一篇: BOM配置