一、需求 最近客户有一个需求，将多个物理表导成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)ASBEGIN
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中
