登录
首页精彩阅读将大数据量导成Execl表思路
将大数据量导成Execl表思路
2017-12-26
收藏

将大数据量导成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中


数据分析咨询请扫描二维码

客服在线
立即咨询