午夜搬运工
做一个作业，夜深人静的时候搬运数据。如下：
USE [myDB]
GO
DECLARE @i INT;
DECLARE @j INT;
DECLARE @m INT;
DECLARE @offset INT;
SELECT @m = ISNULL(MAX(Id),0) FROM [sourceDB].dbo.[Table];
SET @offset = 1000000;--每处理一百万条提交一次
DECLARE @dayLimit SMALLDATETIME;
SET @dayLimit = DATEADD(hh,5,CONVERT(VARCHAR(10),GETDATE(),120));--时限是凌晨 5点
SET IDENTITY_INSERT [Table] ON;--可显式插入标识列
SELECT @i = ISNULL(MAX(Id),0) + 1 FROM [Table];
SET @j = @i + @offset;
WHILE @i<@m
BEGIN
IF @j > @m SET @j = @m;
INSERT INTO [Table]
(……)
SELECT ……
FROM [sourceDB].[dbo].[Table]
WHERE Id BETWEEN @i AND @j;
IF DATEDIFF(hh,GETDATE(),@dayLimit) <= 0 RETURN; --超过时限（凌晨5点）就退出
SET @i = @j + 1;
SET @j = @j + @offset;
END
SET IDENTITY_INSERT [Table] OFF;--取消显式插入标识列