SQL經(jīng)常到的命令

1、USE DBNAMEGO/* Object: StoredProcedure dbo.ExportToExcel Script Date: 03/18/2011 13:16:39 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/* *導(dǎo)出數(shù)據(jù)到EXCEL中且每頁為65535條數(shù)據(jù),包含字段名,文件為真正的Excel文件格式。如果文件不存在,將自動創(chuàng)建文件,存在將不導(dǎo)出。如果表不存在,將自動創(chuàng)建表?；谕ㄓ眯钥紤],僅支持導(dǎo)出標準數(shù)據(jù)類型。-Creator by zoujian 2003.10-增加分頁功能,每頁65000條。-Add by xiex

2、iaoman-修改分頁功能,每頁65535條。-Add by liujunxiong 2011.03.18-1、修改在SQL2005/2008下配置項沒有打開,解決權(quán)限的問題;-2、修改表帶自增列導(dǎo)出報錯的問題。-調(diào)用示例exec ExportToExcel ExcSql='select * from TABLENAME',SavePath='D:',FileName='Test.xls',SheetName='TABLENAME'-存在的問題:-1、文件只能導(dǎo)出到服務(wù)器進行保存。* */CREATE proc dbo.Expor

3、tToExcelExcSql varchar(8000, -查詢語句,如果查詢語句中使用了order by, 請加上top 100 percentSavePath nvarchar(1000,-文件存放目錄FileName nvarchar(250, -文件名SheetName varchar(250='' -要創(chuàng)建的工作表名, 默認為文件名asdeclare err int,src nvarchar(255,desc nvarchar(255,out int在線代理|網(wǎng)頁代理|代理網(wǎng)頁|declare obj int,constr nvarchar(1000,sql varc

4、har(8000,fdlist varchar(8000,tmpsql varchar(8000declare sheetcount int,sheetnow int, recordcount int, recordnow intdeclare sheetsql varchar(8000-創(chuàng)建頁的sqldeclare pagesize intset pagesize = 65535 -Sheet分頁的大小-參數(shù)檢測if isnull(FileName,''='' set FileName='temp.xls'if isnull(SheetName

5、,''='' set SheetName=replace(FileName,'.','#'-檢查文件是否已經(jīng)存在if right(SavePath,1<>'' set SavePath=SavePath+''create table #tb(a bit,b bit,c bitset sql=SavePath+FileNameinsert into #tb exec master.xp_fileexist sql-數(shù)據(jù)庫創(chuàng)建語句set sql=SavePath+FileNameif exi

6、sts(select 1 from #tb where a=1set constr='DRIVER=Microsoft Excel Driver (*.xls;DSN=''''READONLY=FALSE'+'CREATE_DB="'+sql+'"DBQ='+sqlelseset constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES' +'DATABASE=

7、'+sql+'"'-打開配置項exec sp_configure 'show advanced options',1;reconfigure;exec sp_configure 'Ole Automation Procedures',1;reconfigure;exec sp_configure 'Ad Hoc Distributed Queries',1;reconfigure;-連接數(shù)據(jù)庫exec err=sp_oacreate 'adodb.connection',obj outif err

8、<>0 goto lberrexec err=sp_oamethod obj,'open',null,constrif err<>0 goto lberr-創(chuàng)建表的SQLdeclare tbname sysname在線代理|網(wǎng)頁代理|代理網(wǎng)頁|set tbname='#tmp_'+convert(varchar(38,newid(declare tbtmpid nvarchar(50set tbtmpid ='tmp_'+convert(varchar(38,newid(+''-有序列ID tbtmpid的

9、臨時表-2011.03.18-From "Identity(int,1,1" to "newid("set sql='select newid( as '+tbtmpid+', a.* into '+tbname+' from ( select top 100 percent b.* from ( '+ExcSql+' b a'exec(sql-print(sql-取得記錄總數(shù)set recordcount= rowcountif recordcount=0 goto lrtn-print

10、recordcountselect sql='',fdlist=''select fdlist=fdlist+','++'',sql=sql+','++' '+casewhen like '%char'then case when a.length>255 then 'memo'else 'text('+cast(a.length as varchar+'' endwhen li

11、ke '%int' or ='bit' then 'int'when like '%datetime' then 'datetime'when like '%money' then 'money'when like '%text' then 'memo'else endFROM tempdb.syscolumns a left join tempdb.systypes b on a.x

12、type=b.xusertypewhere not in('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp'and a.id=(select id from tempdb.sysobjects where name=tbnameand <> tbtmpidset fdlist=substring(fdlist,2,8000-print fdlist-列數(shù)為零if r

13、owcount=0 goto lrtnset sheetsql = sql-print sheetsql在線代理|網(wǎng)頁代理|代理網(wǎng)頁|-導(dǎo)入數(shù)據(jù),頁數(shù)set sheetcount = CEILING(recordcount/CAST(pagesize as float-print sheetcount-只是一個頁而已IF sheetcount = 1 BEGIN-print '只是一個頁而已'set sql='create table '+SheetName+'('+substring(sheetsql,2,8000+''exec

14、 err=sp_oamethod obj,'execute',out out,sqlif err<>0 goto lberrset sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES;DATABASE='+SavePath+FileName+''','+SheetName+'$'exec('insert into '+sql+'('+fdlist+&

15、#39; select '+fdlist+' from '+tbname+''END-多個頁set sheetnow = sheetcountset recordnow= 0IF sheetcount > 1 BEGIN-print '多個頁'WHILE sheetnow > 0 BEGIN-創(chuàng)建頁set sql='create table '+SheetName+'_'+ convert(nvarchar(80,sheetcount - sheetnow + 1+'('+sub

16、string(sheetsql,2,8000+''exec err=sp_oamethod obj,'execute',out out,sqlif err<>0 goto lberr-print sql-創(chuàng)建頁endIF sheetnow = sheetcount BEGINset tmpsql ='select top '+str(pagesize+' '+fdlist+' from '+tbname+''set sql='openrowset(''MICROS

17、OFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES在線代理|網(wǎng)頁代理|代理網(wǎng)頁|;DATABASE='+SavePath+FileName+''','+SheetName+'_'+convert(nvarchar(80,sheet count - sheetnow + 1+'$'exec('insert into '+sql+'('+fdlist+' '+ tmpsqlENDIF sheetnow < shee

18、tcount BEGINset tmpsql='select top '+str(pagesize+' '+fdlist+' from '+tbname+' where '+tbtmpid+' not in ( select top '+str(recordnow-pagesize+' '+tbtmpid+' from '+tbname+''set sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'&#

19、39;,''Excel 8.0;HDR=YES ;DATABASE='+SavePath+FileName+''','+SheetName+'_'+convert(nvarchar(80,sheetcount - sheetnow + 1+'$'exec('insert into '+sql+'('+fdlist+' '+ tmpsql-print (tmpsql-exec(tmpsqlEND-print (tmpsql-exec (tmpsqlset recordnow = pagesize*(sheetcount-sheetnow+2set sheetnow = sheetnow -1ENDENDset sql='drop table '+tbname+''exec(