將大數據量導成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中