SQL2008使用CTE递归查询批量插入500万数据

原文 http://www.cnblogs.com/wjmo520/archive/2013/04/27/3046795.html

一直都使用一般的批量循环插入方法 ，可这效率也太慢了，插入500万条数据执行所需的时间远远超过10分钟。于是度娘了一下 发现可以使用CTE进行递归循环插入

以下是亲测的数据：

1、创建表：

CREATE TABLE Customers (CustomerID INT primary key identity(1,1),
CustomerNumber CHAR(4),
CustomerName VARCHAR(50),
CustomerCity VARCHAR(20))
GO  

2、使用一般批量循环处理

/*使用一般的批量循环插入*/
declare @maxSum int,
        @customernum nvarchar(50),
        @customername nvarchar(50),
        @city nvarchar(50),
        @paytime datetime
set @maxSum=1
set @customernum='0000'
set @customername='Customer 0'
set @city='X-City'
while @maxSum<5000000  /*一般方法循环插入500万条数据 10分钟以上*/
begin
   insert into Customers (CustomerNumber,CustomerName,CustomerCity)
                 values(@customernum,@customername,@city)
   set @maxSum=@maxSum+1
end
select [500万数据量插入完毕，共花费时间(毫秒)]=datediff(ms,@paytime,getdate())  

结果：（10分钟已过 还没添加完毕...）

3、使用CTE方法

/*使用CTE递归循环插入 运用CTE递归插入，速度较快*/
TRUNCATE table Customers
GO
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE  

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO  

DECLARE @d Datetime
SET @d=getdate();  

WITH Seq (num,CustomerNumber, CustomerName, CustomerCity) AS
(SELECT 1,'0000','Customer 0',cast('X-City' as NVARCHAR(20))
UNION ALL
SELECT num + 1,'0000','Customer 0',
cast(CHAR(65 + (num % 26)) + '-City' AS NVARCHAR(20))
FROM Seq
WHERE num <= 5000000  /*CTE批量递归500万数据量 用时一般大概3分钟 因个人电脑而已*/
)
INSERT INTO Customers (CustomerNumber, CustomerName, CustomerCity)
SELECT CustomerNumber, CustomerName, CustomerCity
FROM Seq
OPTION (MAXRECURSION 0)  

select [500万数据量插入完毕，共花费时间(毫秒)]=datediff(ms,@d,getdate())  

SET STATISTICS IO OFF ;
SET STATISTICS TIME OFF;
GO  

结果：（185秒 约3分钟左右）

网上也查了一下 说：CTE其实是面向对象的，运行的基础是CLR。看来递归 CTE 可以极大地简化在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句中运行递归查询所需的代码。

小记一下 回去再研究研究 下班！