Sql Server 分批复制数据

添加辅助列

ALTER TABLE dbo.SourceTable ADD IsCopied BIT NOT NULL DEFAULT 0;

这里要给源表添加一个标识列，是因为我不想用到ORDER BY。当然也可以改成不用辅助列，用ORDER BY的方式。

创建目标表

SELECT * INTO DestinationTable FROM dbo.SourceTable WHERE 1=0;

当条件不满足0时，仅复制表结构

分批复制数据

DECLARE @BatchSize INT=1000;
DECLARE @RowsAffected INT;
DECLARE @InsertedPrimaryKeys TABLE (Id BIGINT NOT NULL);

WHILE 1=1
BEGIN
    BEGIN TRANSACTION
    INSERT INTO dbo.DestinationTable
    OUTPUT inserted.Id INTO @InsertedPrimaryKeys
    SELECT TOP (@BatchSize) * FROM dbo.SourceTable WHERE IsCopied=0;
    SET @RowsAffected=@@ROWCOUNT;
    IF @RowsAffected>0
    BEGIN
        UPDATE dbo.SourceTable SET IsCopied=1 WHERE IsCopied=0 AND Id IN (SELECT Id FROM @InsertedPrimaryKeys);
        COMMIT TRANSACTION;
    END
    ELSE
    BEGIN
        ROLLBACK TRANSACTION;
        BREAK;
    END    
END

1、取BatchSize条数据插入到新表，并将Id保存到InsertedPrimaryKeys。

2、将插入行的数量保存到RowsAffected。

3、如果有插入行，根据Id把标识列改成已复制

4、当未插入任何行时，循环结束

删除辅助列

ALTER TABLE dbo.SourceTable DROP COLUMN IsCopied;
ALTER TABLE dbo.DestinationTable DROP COLUMN IsCopied;

这里目标表也是有标识列的，一并删除