使用游标更新两表时父表ID插入子表的SCOPE_IDENTITY异常问题

你遇到的核心问题是：当你向@TableCode1插入数据时，因为GROUP BY MonthVal, Code1, Code2，同一组MonthVal+Code1下会生成多条父表记录，而SCOPE_IDENTITY()只能返回这一批插入操作中最后一条记录的ID，导致后续插入@TableCode2时，所有子表记录都绑定到了这个“最后一条父ID”上，完全不符合分组对应关系。

要解决这个问题，我们需要在插入@TableCode1时，把每一条插入的ID和对应的Code2（分组标识）一起捕获下来，然后用这个捕获的结果集关联插入@TableCode2，确保每个子表记录都对应正确的父表ID。

修改后的完整代码如下：

DECLARE @Table1 TABLE ( id INT ,custId INT ,MonthVal INT ,Code1 INT ,Code2 INT ,Val1 INT ,Val2 INT );
INSERT INTO @Table1 (id ,custId ,MonthVal ,Code1 ,Code2 ,Val1 ,Val2)
VALUES (11, 1, 10, 1500, 201, 1, 10) ,(12, 1, 10, 1500, 301, 2, 20) ,(13, 1, 11, 2000, 301, 3, 30) ,(14, 1, 10, 1500, NULL, 5, 50) ,(15, 1, 10, 1500, NULL, 5, 50);

DECLARE @TableCode1 TABLE ( id INT IDENTITY(1, 1) ,MonthVal INT ,Code1 INT ,Val INT );
DECLARE @TableCode2 TABLE ( id INT IDENTITY(1, 1) ,Table1Id INT ,Code2 INT ,Val INT );

-- 新增表变量，用于捕获插入@TableCode1的ID和对应的Code2
DECLARE @InsertedTableCode1 TABLE (InsertedId INT, Code2 INT, MonthVal INT, Code1 INT);

DECLARE @MonthVal INT;
DECLARE @Code1 INT;
DECLARE cursor_product CURSOR FOR SELECT DISTINCT MonthVal ,Code1 FROM @Table1;

OPEN cursor_product;
FETCH NEXT FROM cursor_product INTO @MonthVal ,@Code1;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 插入@TableCode1时，用OUTPUT把插入的ID和分组用的Code2、MonthVal、Code1存入临时表变量
    INSERT INTO @TableCode1 (MonthVal ,Code1 ,Val)
    OUTPUT inserted.id, t.Code2, t.MonthVal, t.Code1 INTO @InsertedTableCode1(InsertedId, Code2, MonthVal, Code1)
    SELECT t.MonthVal ,t.Code1 ,SUM(t.Val1) 
    FROM @Table1 t 
    WHERE t.MonthVal = @MonthVal AND t.Code1 = @Code1 
    GROUP BY t.MonthVal ,t.Code1 ,t.Code2;

    -- 插入@TableCode2时，关联捕获的临时表，获取正确的父表ID
    INSERT INTO @TableCode2 (Code2 ,Val ,Table1Id)
    SELECT t.Code2, SUM(t.Val2), itc.InsertedId
    FROM @Table1 t
    JOIN @InsertedTableCode1 itc 
        ON t.MonthVal = itc.MonthVal 
        AND t.Code1 = itc.Code1 
        AND ISNULL(t.Code2, '') = ISNULL(itc.Code2, '') -- 处理Code2为NULL的情况
    WHERE t.MonthVal = @MonthVal AND t.Code1 = @Code1
    GROUP BY t.MonthVal, t.Code1, t.Code2, itc.InsertedId;

    -- 清空临时表变量，准备下一轮游标循环
    DELETE FROM @InsertedTableCode1;

    FETCH NEXT FROM cursor_product INTO @MonthVal ,@Code1;
END;

CLOSE cursor_product;
DEALLOCATE cursor_product;

SELECT * FROM @TableCode1;
SELECT * FROM @TableCode2;

• 新增@InsertedTableCode1表变量：用来存储每一次插入@TableCode1时生成的ID，以及对应的分组字段Code2、MonthVal、Code1，这样我们能精准关联每个父表记录对应的分组。
• 使用OUTPUT子句捕获插入数据：在INSERT INTO @TableCode1时，通过OUTPUT inserted.id, t.Code2...把插入的ID和原表的分组标识一起存入临时表，确保每个父ID都对应正确的Code2。
• 关联插入子表：插入@TableCode2时，通过JOIN @InsertedTableCode1匹配对应的分组字段，获取正确的父表ID，避免了SCOPE_IDENTITY()只能取最后一条的问题。
• 处理Code2为NULL的情况：用ISNULL(t.Code2, '') = ISNULL(itc.Code2, '')来匹配NULL值，因为NULL和NULL直接用=是不相等的，需要特殊处理。

这样修改后，@TableCode2的Table1Id就能正确对应@TableCode1中同分组的记录ID了。

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