如何将MERGE的NOT MATCHED结果插入两个表，避免带入更新数据？

MERGE语句的OUTPUT子句默认会捕获所有执行的操作结果——不管是WHEN MATCHED触发的更新，还是WHEN NOT MATCHED触发的插入。你当前的代码没有过滤操作类型，所以更新后的行也会被插入到test表中。

要实现仅将WHEN NOT MATCHED的插入结果同步到两个表，可按以下方式修改：

1. 利用$action函数过滤仅INSERT操作的行，该函数会返回当前行对应的操作类型（'UPDATE'或'INSERT'）
2. 先将插入结果暂存到表变量，再从表变量分别插入到两个目标表

修改后的存储过程代码：

ALTER PROCEDURE [dbo].[identify]
AS
BEGIN
    SET NOCOUNT ON;

    -- 定义表变量存储仅插入的行
    DECLARE @InsertedRows TABLE (
        first_name VARCHAR(50),
        last_name VARCHAR(50)
    );

    MERGE INTO [test].[dbo].[warehouse] AS dim
    USING [test].[dbo].[staging] AS stg
        ON dim.[first_name] = stg.first_name
    WHEN MATCHED THEN
        UPDATE SET
            dim.[first_name] = stg.first_name,
            dim.last_name = stg.last_name,
            dim.created_date = stg.created_date,
            dim.modified_date = stg.modified_date,
            dim.gender = stg.gender
    WHEN NOT MATCHED THEN
        INSERT(first_name, last_name, created_date, modified_date, gender)
        VALUES(stg.first_name, stg.last_name, stg.created_date, stg.modified_date, stg.gender)
    -- 仅捕获INSERT操作的行存入表变量
    OUTPUT Inserted.first_name, Inserted.last_name
    INTO @InsertedRows
    WHERE $action = 'INSERT';

    -- 插入第一个目标表test
    INSERT INTO test (first_name, last_name)
    SELECT first_name, last_name FROM @InsertedRows;

    -- 插入第二个目标表（替换为你的实际表名）
    INSERT INTO your_second_target_table (first_name, last_name)
    SELECT first_name, last_name FROM @InsertedRows;

END

关键说明 #

• WHERE $action = 'INSERT'确保只有插入的行被存入表变量，彻底排除更新操作的结果
• 通过表变量中转，实现一次捕获结果、多次插入到不同目标表的需求
• INSERT的VALUES子句添加stg.前缀，避免列名歧义问题

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