SQL Server(SSMS)中基于表A路径同步B并插入C的实现方法问询

问题背景 #

表结构 #

A: AId, Name, Color, Path
B: BId, Name, Color, Path, PathId（自增非空列）
C: AId, Name, Color, PathId 

业务需求 #

针对表A的每条记录：

• 若表B中存在相同Path值，直接获取该Path对应的PathId，将A的相关字段和此PathId插入表C；
• 若表B中不存在该Path，先将A的Name、Color、Path插入表B，再获取生成的自增PathId，将A的相关字段和此PathId插入表C。

现有卡点 #

用户尝试用MERGE语句实现，但无法在匹配分支中正确获取表B的PathId并插入表C，现有代码如下：

DECLARE @Temp table (
       [TempId] [varchar] (255) NOT NULL,
       [Name] [varchar] (255) NOT NULL,
       [Color] [varchar] (255) NULL,
       [PathId] [int] IDENTITY(1,1) NOT NULL
)

--I want to insert values in @Temp table who have Path in TableB. 
--I am not sure how do I get the AId and the other values

MERGE Schema2.B AS B
USING Schema1.A AS A
ON B.Path= A.Path

WHEN MATCHED THEN
    INSERT Schema1.C(AId, [Name], [Color], [PathId])
    VALUES () --Stuck here (I want to add values from @Temp table)

WHEN NOT MATCHED THEN
INSERT ([Name], [Color], [Path])
VALUES (A.Tenant, A.CustomerId, A.AmsPath);

解决方案 #

利用SQL Server的MERGE结合OUTPUT子句，可以一次性捕获所有匹配/新增的PathId，再批量插入表C，无需复杂的临时表操作。具体代码如下：

-- 声明临时表存储MERGE结果，关联A表字段与对应PathId
DECLARE @MergeResults TABLE (
    AId VARCHAR(255), -- 需与表A的AId数据类型保持一致
    Name VARCHAR(255),
    Color VARCHAR(255),
    PathId INT
);

MERGE Schema2.B AS TargetTable
USING Schema1.A AS SourceTable
ON TargetTable.Path = SourceTable.Path

-- 匹配分支：空更新触发OUTPUT，捕获A表字段与B表已存在的PathId
WHEN MATCHED THEN
    UPDATE SET TargetTable.Name = TargetTable.Name -- 无实际更新，仅为触发OUTPUT
    OUTPUT 
        SourceTable.AId,
        SourceTable.Name,
        SourceTable.Color,
        TargetTable.PathId
    INTO @MergeResults

-- 不匹配分支：插入新数据到B表，捕获A表字段与新生成的PathId
WHEN NOT MATCHED THEN
    INSERT (Name, Color, Path)
    VALUES (SourceTable.Name, SourceTable.Color, SourceTable.Path) -- 修正原代码字段匹配问题，需与表B字段对应
    OUTPUT 
        SourceTable.AId,
        SourceTable.Name,
        SourceTable.Color,
        inserted.PathId
    INTO @MergeResults;

-- 批量插入结果到表C
INSERT INTO Schema1.C(AId, Name, Color, PathId)
SELECT AId, Name, Color, PathId
FROM @MergeResults;

关键说明 #

1. 临时表@MergeResults：作为中间载体，统一存储所有需要插入表C的记录，不管是匹配已存在Path的情况，还是新增Path的情况。
2. MATCHED分支的空更新：MERGE的匹配分支必须包含UPDATE或DELETE操作才能使用OUTPUT，这里的空更新（字段赋值给自己）不会修改数据，仅为了触发输出逻辑，获取已存在的PathId。
3. NOT MATCHED分支的inserted.PathId：插入表B后，通过inserted关键字获取自增生成的PathId。
4. 字段匹配修正：原代码中VALUES (A.Tenant, A.CustomerId, A.AmsPath)属于字段匹配错误，需替换为表A对应的Name、Color、Path字段，若实际业务中字段对应关系不同，请自行调整。

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