1亿+记录数据库表拆分至两表：现有方案过慢求优化方法

我数据库内有一张包含100,050,000条记录的表，需拆分至两张新表，现寻求高效实现方案。我已尝试如下SQL脚本：

DECLARE @from BIGINT = 0,
        @step BIGINT = 1000000,
        @currentSourceCount BIGINT = 0

SELECT @currentSourceCount = COUNT_BIG(1) FROM dbo.SourceTable

WHILE @from < @currentSourceCount
BEGIN       
    INSERT INTO dbo.DestinationTable WITH (TABLOCKX)(col1, col2, col3, col4)
    SELECT t1.col1,
            t1.col2,
            t1.col3,
            t1.col4
    FROM (SELECT a.col1,
                    a.col2,
                    a.col3,
                    a.col4
            FROM (  SELECT st.col1,
                        st.col2,
                        st.col3,
                        st.col4,
                        ROW_NUMBER() OVER (ORDER BY st.Id) AS RowNumber
                FROM dbo.SourceTable st) a
    ) AS t1
    WHERE t1.RowNumber BETWEEN @from AND @from + @step

    SET @from += @step + 1
END

但该方案速度过慢，仅在4小时内完成约1/3的数据拆分。已移除表的外键，仅保留设为IDENTITY的主键，且每次循环会向两张新表插入不同数据，请问是否有更快的实现方法？

1. 基于主键范围批量插入，规避全表扫描 #

原脚本每次循环都要对全表执行ROW_NUMBER()排序，这是核心性能瓶颈。直接利用IDENTITY主键Id的连续性分批次处理：

DECLARE @minId BIGINT, @maxId BIGINT, @batchSize BIGINT = 1000000
SELECT @minId = MIN(Id), @maxId = MAX(Id) FROM dbo.SourceTable

DECLARE @currentStartId BIGINT = @minId
WHILE @currentStartId <= @maxId
BEGIN
    DECLARE @currentEndId BIGINT = @currentStartId + @batchSize - 1
    IF @currentEndId > @maxId SET @currentEndId = @maxId

    -- 插入第一张目标表，根据拆分规则调整WHERE条件
    INSERT INTO dbo.DestTable1 WITH (TABLOCKX) (col1, col2, col3, col4)
    SELECT col1, col2, col3, col4
    FROM dbo.SourceTable
    WHERE Id BETWEEN @currentStartId AND @currentEndId
    -- 示例拆分条件：Id % 2 = 0

    -- 插入第二张目标表
    INSERT INTO dbo.DestTable2 WITH (TABLOCKX) (col1, col2, col3, col4)
    SELECT col1, col2, col3, col4
    FROM dbo.SourceTable
    WHERE Id BETWEEN @currentStartId AND @currentEndId
    -- 示例拆分条件：Id % 2 = 1

    SET @currentStartId = @currentEndId + 1
END

该方法每次仅扫描主键范围内的行，避免全表遍历，性能会大幅提升。

2. 启用批量插入优化 #

• 禁用目标表的非主键索引，完成所有插入后再重建索引——边插边维护索引的开销远高于事后重建。
• 保留TABLOCKX提示，减少锁竞争，触发SQL Server的最小日志批量插入优化。
• 若目标表无需IDENTITY列，关闭IDENTITY_INSERT，避免额外验证开销。

3. 开启并行查询（SQL Server 2016+） #

针对单批次插入操作，设置并行度充分利用CPU资源：

INSERT INTO dbo.DestTable1 WITH (TABLOCKX) (col1, col2, col3, col4)
SELECT col1, col2, col3, col4
FROM dbo.SourceTable
WHERE Id BETWEEN @currentStartId AND @currentEndId
OPTION (MAXDOP 8) -- 根据服务器CPU核心数调整并行度

同时确保数据库max degree of parallelism配置允许并行操作。

4. 分区切换（超大规模数据首选） #

如果源表已分区，直接将对应分区切换到目标表，操作近乎瞬时。若源表未分区，可临时创建分区表导入数据后再切换，适合亿级以上数据拆分。

5. 降低事务日志开销 #

• 将数据库恢复模式临时改为BULK_LOGGED，批量插入仅记录最小日志，大幅减少IO压力，操作完成后改回原模式。
• 每个批次作为独立事务，避免单个大事务占用过多日志空间。

内容的提问来源于stack exchange，提问作者Davecz