多对多关联中间表的SQL MERGE语句实现疑问

核心思路 #

先通过MERGE完成Address表的增改，用OUTPUT子句捕获所有处理后的地址ID；再以「当前公司ID + 已处理地址ID」作为源数据，对中间表CompanyAddress做MERGE，实现关联关系的同步（新增缺失关联、删除无效关联）。

步骤1：处理Address表并捕获已处理地址ID #

因为输入的JSON地址没有addrId，必须靠地址的唯一识别字段（比如街道、城市、邮编的组合）来判断是否为同一地址，用MERGE完成增改后，把生成/匹配到的addrId存入临时表/表变量。

示例代码（假设JSON含street, city, zipCode字段，存储过程参数为@CoId INT和@AddressJsonArray NVARCHAR(MAX)）：

-- 定义表变量存储处理后的地址ID与唯一标识
DECLARE @ProcessedAddresses TABLE (
    addrId INT,
    street VARCHAR(100),
    city VARCHAR(50),
    zipCode VARCHAR(20)
);

-- MERGE Address表：匹配则更新，不匹配则插入
MERGE INTO Address AS target
USING (
    SELECT 
        JSON_VALUE(item, '$.street') AS street,
        JSON_VALUE(item, '$.city') AS city,
        JSON_VALUE(item, '$.zipCode') AS zipCode
    FROM OPENJSON(@AddressJsonArray) AS items
) AS source
-- 按地址唯一特征匹配
ON target.street = source.street 
AND target.city = source.city 
AND target.zipCode = source.zipCode
WHEN MATCHED THEN 
    UPDATE SET 
        street = source.street,
        city = source.city,
        zipCode = source.zipCode
WHEN NOT MATCHED THEN
    INSERT (street, city, zipCode)
    VALUES (source.street, source.city, source.zipCode)
-- 将处理后的addrId和地址特征输出到表变量
OUTPUT inserted.addrId, source.street, source.city, source.zipCode INTO @ProcessedAddresses;

步骤2：MERGE同步中间表CompanyAddress #

源数据直接用当前公司的@CoId结合@ProcessedAddresses里的所有addrId，通过MERGE实现：

• 新增当前公司与已处理地址的缺失关联
• 删除当前公司与未在本次处理地址中的无效关联

示例代码：

MERGE INTO CompanyAddress AS target
USING (
    SELECT 
        @CoId AS coId,
        pa.addrId
    FROM @ProcessedAddresses pa
) AS source
-- 按公司ID+地址ID匹配关联关系
ON target.coId = source.coId 
AND target.addrId = source.addrId
-- 不存在的关联则插入
WHEN NOT MATCHED THEN
    INSERT (coId, addrId)
    VALUES (source.coId, source.addrId)
-- 公司存在但地址不在本次处理列表中的关联则删除
WHEN NOT MATCHED BY SOURCE AND target.coId = @CoId THEN
    DELETE;

关键注意点 #

• 地址的唯一识别字段必须准确，否则会导致重复插入或错误更新
• 必须用MERGE的OUTPUT子句捕获addrId，这是关联中间表的核心依据
• 中间表的MERGE要限定NOT MATCHED BY SOURCE的范围为当前公司，避免误删其他公司的关联

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