请求创建SQL Server中插入Table1后用插入标识更新Table2的TSQL语句

要实现先向Table1插入数据，再利用插入生成的Identity字段更新Table2的需求，SQL Server（TSQL）无法像PostgreSQL那样直接在CTE中用RETURNING关联更新，需要借助表变量捕获插入结果后执行更新，具体实现如下：

-- 声明表变量，存储插入到tbl1的记录及生成的Identity值
DECLARE @InsertedRecords TABLE (
    Tbl1Id INT,
    Col1 VARCHAR(50), -- 替换为实际字段数据类型
    Col2 VARCHAR(50)  -- 替换为实际字段数据类型
);

-- 向tbl1插入数据，同时将生成的ID和关联字段存入表变量
INSERT INTO [dbo].[tbl1] ([Col1], [Col2])
OUTPUT inserted.Tbl1Id, inserted.Col1, inserted.Col2 INTO @InsertedRecords
SELECT 
    [Col1],
    [Col2]
FROM [dbo].[Tbl2]
WHERE ([Tbl2].[Tbl1Id] = 0 OR [Tbl2].[Tbl1Id] IS NULL)
GROUP BY [Col1], [Col2];

-- 用表变量中的数据更新Tbl2的Tbl1Id字段
UPDATE t2
SET t2.Tbl1Id = ir.Tbl1Id
FROM [dbo].[Tbl2] t2
JOIN @InsertedRecords ir 
    ON t2.Col1 = ir.Col1 
    AND t2.Col2 = ir.Col2
WHERE (t2.Tbl1Id = 0 OR t2.Tbl1Id IS NULL);

逻辑说明 #

1. 表变量@InsertedRecords用来捕获插入操作产生的Tbl1Id（Identity字段）以及用于关联的Col1、Col2字段。
2. INSERT语句通过OUTPUT...INTO子句将插入结果写入表变量，这是SQL Server中捕获插入生成值的标准方式。
3. 最后通过JOIN表变量匹配关联字段，将Tbl2中对应记录的Tbl1Id更新为刚生成的Identity值。

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