替代循环执行INSERT-SELECT-UPDATE序列操作的高效方案

嘿，我之前也踩过循环和游标处理批量数据的坑，17000条其实不算大，慢成那样完全是逐行操作拖了后腿。给你几个基于集合式操作的替代思路，性能会提升几个数量级：

方案1：用OUTPUT子句批量捕获插入ID，一次性更新TABLE2 #

这是最推荐的方案，利用SQL的OUTPUT子句在插入时直接捕获所有新生成的主键，配合临时表做批量更新，全程都是数据库优化后的集合操作。

-- 1. 创建临时表，存储新插入的ID和MASTERTABLE中用于关联TABLE2的字段
CREATE TABLE #InsertedRecords (NewTable1Id INT, MasterSourceKey INT);

-- 2. 批量插入TABLE1，同时把新ID和关联字段写入临时表
INSERT INTO TABLE1 (Col1, Col2, Col3, Col4, Col5, Col6)
OUTPUT inserted.Id, MASTERTABLE.SourceKey INTO #InsertedRecords
SELECT Col1, Col2, Col3, Col4, Col5, Col6
FROM MASTERTABLE;

-- 3. 用临时表批量更新TABLE2
UPDATE t2
SET t2.LinkedTable1Id = ir.NewTable1Id
FROM TABLE2 t2
INNER JOIN #InsertedRecords ir 
    ON t2.MatchingKey = ir.MasterSourceKey;

-- 清理临时表
DROP TABLE #InsertedRecords;

这个方案的核心是避免逐行获取ID和调用更新，所有操作都是批量执行，17000条数据应该在几十秒内就能完成。

方案2：如果必须用存储过程，用表值参数批量传递数据 #

如果更新TABLE2的逻辑必须封装在存储过程里，别逐行调用存储过程，而是用表值参数把所有需要更新的ID和关联字段一次性传递进去，让存储过程内部做批量处理。

首先定义表值类型：

CREATE TYPE Table1IdMapping AS TABLE (
    NewId INT,
    SourceKey INT
);

然后修改存储过程接受这个参数：

CREATE PROCEDURE UpdateTable2InBatch
    @IdMappings Table1IdMapping READONLY
AS
BEGIN
    SET NOCOUNT ON;

    -- 内部批量更新
    UPDATE t2
    SET t2.LinkedTable1Id = im.NewId
    FROM TABLE2 t2
    INNER JOIN @IdMappings im 
        ON t2.MatchingKey = im.SourceKey;
END;

最后调用时批量传递数据：

DECLARE @InsertedMappings Table1IdMapping;

-- 插入TABLE1并捕获映射关系
INSERT INTO TABLE1 (Col1, Col2, Col3, Col4, Col5, Col6)
OUTPUT inserted.Id, MASTERTABLE.SourceKey INTO @InsertedMappings
SELECT Col1, Col2, Col3, Col4, Col5, Col6
FROM MASTERTABLE;

-- 调用存储过程批量更新
EXEC UpdateTable2InBatch @IdMappings = @InsertedMappings;

方案3：自增主键范围判断（适合无并发场景） #

如果TABLE1的主键是自增IDENTITY，且插入期间没有其他进程往TABLE1写数据，可以用插入前后的ID范围来定位新行，再关联更新：

-- 记录插入前TABLE1的最大主键值
DECLARE @StartId INT = IDENT_CURRENT('TABLE1');

-- 批量插入TABLE1
INSERT INTO TABLE1 (Col1, Col2, Col3, Col4, Col5, Col6)
SELECT Col1, Col2, Col3, Col4, Col5, Col6
FROM MASTERTABLE;

-- 记录插入后的最大主键值
DECLARE @EndId INT = IDENT_CURRENT('TABLE1');

-- 通过MASTERTABLE关联TABLE1的新行，批量更新TABLE2
UPDATE t2
SET t2.LinkedTable1Id = t1.Id
FROM TABLE2 t2
INNER JOIN MASTERTABLE mt 
    ON t2.MatchingKey = mt.SourceKey
INNER JOIN TABLE1 t1 
    ON t1.Col1 = mt.Col1 
    AND t1.Col2 = mt.Col2 
    -- 这里要匹配MASTERTABLE和TABLE1的所有关联字段
WHERE t1.Id BETWEEN @StartId AND @EndId;

⚠️ 注意：这个方案有并发风险，如果插入期间有其他操作往TABLE1插入数据，ID范围会包含不属于本次的行，所以只适合单线程、无并发的场景。

核心思路就是抛弃逐行操作，用数据库擅长的集合式处理，这三个方案都能把你的操作时间从几小时压缩到分钟级甚至更短。

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