將大數(shù)據(jù)量導(dǎo)成Execl表思路

一、需求

最近客戶有一個(gè)需求，將多個(gè)物理表導(dǎo)成Execl表，可是問(wèn)題是其中有幾個(gè)表的數(shù)據(jù)量在20W以上，一個(gè)Execl的Sheet只能導(dǎo)入65536條數(shù)據(jù)，直接使用SQL Server的導(dǎo)出功能，不能實(shí)現(xiàn)該效果。

二、解決思路

從網(wǎng)上搜索相關(guān)的解決辦法，原來(lái)是想有專門(mén)的軟件實(shí)現(xiàn)該功能，用搜狗沒(méi)找到，只找到鄒建的存儲(chǔ)過(guò)程，能夠通用將一張表里的數(shù)據(jù)導(dǎo)入到一張Execl的多個(gè)Sheet中，相關(guān)的代碼大家可以搜索網(wǎng)絡(luò)，這里就不在帖出來(lái)了，但是有一個(gè)問(wèn)題，就是如果一個(gè)表的數(shù)據(jù)大于20W的時(shí)候，這個(gè)存儲(chǔ)過(guò)程執(zhí)行會(huì)出錯(cuò)，出現(xiàn)“超出資源”。

于是自己想了一個(gè)辦法，分兩步來(lái)解決導(dǎo)出到Execl的問(wèn)題，第一步，將一個(gè)大數(shù)據(jù)量的表格，按照6W一個(gè)表分拆成多個(gè)表格。第二步利用SQL Server本身的導(dǎo)出功能，將多個(gè)表格的數(shù)據(jù)導(dǎo)入到一個(gè)Execl中。

三、解決辦法

第一步采用一個(gè)通用的存儲(chǔ)過(guò)程將一個(gè)物理表拆分成多個(gè)表格，存儲(chǔ)過(guò)程如下：
 -- =============================================
-- Author:  George
-- Description: 為了能將大數(shù)據(jù)量的表導(dǎo)出到Execl，將表按照6w的規(guī)模拆分成多個(gè)表
-- Sample: exec sp_splittable 'tableName' --會(huì)按照原來(lái)的表名稱拆分成多個(gè)表，拆分的表名稱后綴通過(guò)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 --每個(gè)表的大小
 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 --只有一個(gè)表格
  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本身的導(dǎo)出功能，將多個(gè)表格的數(shù)據(jù)導(dǎo)入到一個(gè)Execl中