将大数据量导成Execl表思路

一、需求

最近客户有一个需求，将多个物理表导成Execl表，可是问题是其中有几个表的数据量在20W以上，一个Execl的Sheet只能导入65536条数据，直接使用SQL Server的导出功能，不能实现该效果。

二、解决思路

从网上搜索相关的解决办法，原来是想有专门的软件实现该功能，用搜狗没找到，只找到邹建的存储过程，能够通用将一张表里的数据导入到一张Execl的多个Sheet中，相关的代码大家可以搜索网络，这里就不在帖出来了，但是有一个问题，就是如果一个表的数据大于20W的时候，这个存储过程执行会出错，出现“超出资源”。

于是自己想了一个办法，分两步来解决导出到Execl的问题，第一步，将一个大数据量的表格，按照6W一个表分拆成多个表格。第二步利用SQL Server本身的导出功能，将多个表格的数据导入到一个Execl中。

三、解决办法

第一步采用一个通用的存储过程将一个物理表拆分成多个表格，存储过程如下：
 -- =============================================
-- Author:  George
-- Description: 为了能将大数据量的表导出到Execl，将表按照6w的规模拆分成多个表
-- Sample: exec sp_splittable 'tableName' --会按照原来的表名称拆分成多个表，拆分的表名称后缀通过1，2，3
-- =============================================
CRTEATE PROCEDURE [dbo].[SP_SPLITETABLE]
 @tableName varchar(100)
AS
BEGIN

 declare @rows int,@temptable varchar(100),@sql varchar(500)
 declare @insertSql varchar(1000)
 declare @tablenum varchar(100)

 set @temptable='temp'+convert(varchar(38),newid())
 set @sql='select Identity(int,1,1) as tempid,* into ['+@temptable+'] from '+@tableName
 exec(@sql)
 
 set @rows=@@ROWCOUNT
 if @rows=0 return

 declare @tablecount int,@tablenow int, @recordcount int, @recordnow int
 declare @pagesize int
 declare @tableindex int
 
 set @pagesize = 60000 --每个表的大小
 set @tableindex =1

 set @tablecount = CEILING(@rows/CAST(@pagesize as float))

 set @tablenow = @tablecount
 set @recordnow= 0

 IF @tablecount = 1 return
 IF @tablecount > 1 begin

 WHILE @tablenow > 1 begin
 
 set @tablenum=@tableName+rtrim(ltrim(str(@tableindex)))
 
 if @tablenow=@tablecount begin --只有一个表格
  set @insertSql='select top '+rtrim(ltrim(str(@pagesize)))+' * into '+@tablenum+' from ['+@temptable+']'
  exec (@insertSql)
 end
 
 IF @tablenow < @tablecount begin
 set @insertSql='select top '+rtrim(ltrim(str(@pagesize)))+' * into '+@tablenum+' from ['+@temptable +'] where tempid not in (select top '+rtrim(ltrim(str(@recordnow-@pagesize)))+' tempid from ['+@temptable+'] )'
 exec (@insertSql)
 end
 
    set @recordnow = @pagesize*(@tablecount-@tablenow+2)
    set @tablenow = @tablenow -1
    set @tableindex=@tableindex+1
 END
 END
 set @sql='delete from ['+@temptable+']'
 exec(@sql)
 
END

第二步利用SQL Server本身的导出功能，将多个表格的数据导入到一个Execl中