-- 定义循环变量
DECLARE @ROWS INT ,
@ROWS_Limit INT ,
@ROWS_BATCH INT ,
@ROWS_COUNT INT ;
-- 设置循环变量参数
SELECT @ROWS = 0 ,
@ROWS_Limit = 9517792 ,
@ROWS_BATCH = 1000 ,
@ROWS_COUNT = @ROWS_BATCH ;
-- 创建临时表变量,记录操作数数据全部主键值
IF OBJECT_ID('tempdb..#temp001') IS NOT NULL
BEGIN
DROP TABLE #temp001
END
-- 创建临时表变量,记录每批次操作记录主键值
IF OBJECT_ID('tempdb..#temp002') IS NOT NULL
BEGIN
DROP TABLE #temp002
END
CREATE TABLE #temp001
(
TransactionNumber INT PRIMARY KEY ,
) ;
CREATE TABLE #temp002
(
TransactionNumber INT PRIMARY KEY ,
) ;
-- 按条件导入需要操作数据的主键值
INSERT INTO ##temp001
SELECT TransactionNumber
FROM TABLE01
WHILE @ROWS_COUNT = @ROWS_BATCH
AND @ROWS < @ROWS_Limit
BEGIN;
-- 迁移数据
INSERT INTO @TABLE01 A
OUTPUT INSERTED.TransactionNumber INTO #temp002
SELECT TOP (@row_batch) FROM @TABLE02
INNER JOIN #temp001 C ON A.TransactionNumber = C.TransactionNumber
WHERE InDate BETWEEN @FromDate AND @EndDate
-- 删除每批次迁移完成数据
DELETE FROM #temp001 A WHERE TransactionNumber EXISTS
(SELECT TOP 1 1 FROM #temp002 B WHERE A.TransactionNumber = B.TransactionNumber )
SELECT @row_count = @@ROWCOUNT ,
@ROWS = @ROWS + @row_count ;
-- 截断临时表
TRUNCATE TABLE #temp002
WAITFOR DELAY '00:00:10' ;
END ;
在处理大数据量数据时,可以按照目前的方式批量处理数据