用数据库内置对象批量导出数据到excel的问题?
在网上找到一个批量导出数据到excel的存储过程,发现导出的数据前面总是有一个'号，每一个单元格数据前面都有一个'号，请教下大家有没有什么办法去除这个上引号呢？还有一个问题是如果导出的数据太多，如10万条数据，一个调用这个存储过程，sqlserver在导出数据的这段时刻占用cpu将很高，那么如果多个人同时导出，服务器将长时间cpu占用很高，有没有办法解决cpu占用很高的问题呢？
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo/*--数据导出EXCEL 导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件 如果文件不存在,将自动创建文件 如果表不存在,将自动创建表 基于通用性考虑,仅支持导出标准数据类型--邹建 2003.10(引用请保留此信息)--增加分页功能6.5w条一页--Add by 谢小漫--*//*--调用示例 p_exporttb @sqlstr='select top 140000 * from taoluTable' ,@path='D:\',@fname='aa.xls',@sheetname=''--*/ALTER proc [dbo].[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 intdeclare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000),@tmpsql varchar(8000)declare @sheetcount int,@sheetnow int, @recordcount int, @recordnow intdeclare @sheetsql varchar(8000)--创建页的sqldeclare @pagesize intset @pagesize = 65000--sheet分页的大小--set @pagesize = 1000--参数检测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+@fnameinsert into #tb exec master..xp_fileexist @sql--数据库创建语句set @sql=@path+@fnameif exists(select 1 from #tb where a=1) set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE' +';CREATE_DB="'+@sql+'";DBQ='+@sqlelse set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES' +';DATABASE='+@sql+'"'--连接数据库exec @err=sp_oacreate 'adodb.connection',@obj outif @err<>0 goto lberrexec @err=sp_oamethod @obj,'open',null,@constrif @err<>0 goto lberr--创建表的SQLdeclare @tbname sysnameset @tbname='##tmp_'+convert(varchar(38),newid())declare @tbtmpid nvarchar(50)set @tbtmpid ='tmp_'+convert(varchar(38),newid())+''--有序列ID @tbtmpid的临时表set @sql='select Identity(int,1,1) as ['+@tbtmpid+'], a.* into ['+@tbname+'] from ( select top 100 percent b.* from ( '+@sqlstr+') b) a'exec(@sql)--print(@sql)--取得记录总数set @recordcount= @@rowcountif @recordcount=0 return--print @recordcountselect @sql='',@fdlist=''select @fdlist=@fdlist+',['+a.name+']' ,@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 endFROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertypewhere b.name not in('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp') and a.id=(select id from tempdb..sysobjects where name=@tbname)and a.name <> @tbtmpidset @fdlist=substring(@fdlist,2,8000)--print @fdlist--列数为零if @@rowcount=0 returnset @sheetsql = @sql--print @sheetsql--导入数据--页数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 @err=sp_oamethod @obj,'execute',@out out,@sql if @err<>0 goto lberr set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES ;DATABASE='+@path+@fname+''',['+@sheetname+'$])' exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']')END--多个页set @sheetnow = @sheetcountset @recordnow= 0 IF @sheetcount > 1 BEGIN --print '多个页'WHILE @sheetnow > 0 BEGIN --创建页 set @sql='create table ['+@sheetname+'_'+ convert(nvarchar(80),@sheetcount - @sheetnow + 1) +']('+substring(@sheetsql,2,8000)+')' exec @err=sp_oamethod @obj,'execute',@out out,@sql if @err<>0 goto lberr --print @sql --创建页end IF @sheetnow = @sheetcount BEGIN set @tmpsql ='select top '+str(@pagesize)+' '+@fdlist+' from ['+@tbname+']' set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES ;DATABASE='+@path+@fname+''',['+@sheetname+'_'+convert(nvarchar(80),@sheetcount - @sheetnow + 1)+'$])' exec('insert into '+@sql+'('+@fdlist+') '+ @tmpsql) END IF @sheetnow < @sheetcount BEGIN set @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'',''Excel 8.0;HDR=YES ;DATABASE='+@path+@fname+''',['+@sheetname+'_'+ convert(nvarchar(80),@sheetcount - @sheetnow + 1)+'$])' exec('insert into '+@sql+'('+@fdlist+') '+ @tmpsql) --print (@tmpsql) --exec(@tmpsql) END --print (@tmpsql) --exec (@tmpsql) set @recordnow = @pagesize*(@sheetcount-@sheetnow+2) set @sheetnow = @sheetnow -1ENDENDexec sp_oamethod @obj,'close',null--关闭xls连接set @sql='drop table ['+@tbname+']'exec(@sql)exec @err=sp_oadestroy @obj--结束返回returnlberr: exec sp_oageterrorinfo 0,@src out,@desc outlbexit: select cast(@err as varbinary(4)) as 错误号 ,@src as 错误源,@desc as 错误描述 select @sql,@constr,@fdlist