使用SQL MERGE追踪变更：数据库增量更新的变更记录需求

嘿，刚好我之前处理过类似的需求！你完全不用再靠对比新旧表来抓变更——SQL Server的MERGE语句自带的OUTPUT子句就是专门干这个的，能直接捕获MERGE过程中发生的新增、更新、删除所有操作细节，一步到位。

核心思路：用OUTPUT子句捕获变更 #

MERGE执行时会生成两个虚拟表：DELETED（存储变更前的记录，UPDATE和DELETE操作时有数据）和INSERTED（存储变更后的记录，INSERT和UPDATE操作时有数据）。配合$action函数（返回操作类型：INSERT/UPDATE/DELETE），就能把所有变更信息捞出来，要么直接返回结果，要么插入到专门的日志表留存。

修改后的MERGE示例代码 #

假设你的目标表是data_warehouse.dbo.data_target，源表是data_warehouse.dbo.data_source，匹配条件是id字段，我给你改好带追踪的版本：

-- 第一步：先建个变更日志表（如果还没有的话，用来存每日变更记录）
CREATE TABLE IF NOT EXISTS data_warehouse.dbo.merge_change_log (
    action_type NVARCHAR(10) NOT NULL, -- 操作类型：INSERT/UPDATE/DELETE
    target_id INT NOT NULL, -- 目标表主键，方便定位记录
    old_data JSON, -- 存变更前的全量数据（也可以拆成具体字段）
    new_data JSON, -- 存变更后的全量数据
    change_time DATETIME DEFAULT GETDATE() -- 变更发生时间
);

-- 第二步：带OUTPUT的MERGE语句
MERGE data_warehouse.dbo.data_target AS target
USING data_warehouse.dbo.data_source AS source
ON target.id = source.id -- 这里替换成你的实际匹配条件
WHEN MATCHED THEN
    UPDATE SET 
        target.your_column = source.your_column, -- 你的更新字段
        target.last_modified = GETDATE() -- 你的最后修改时间字段
WHEN NOT MATCHED BY TARGET THEN
    INSERT (id, your_column, last_modified)
    VALUES (source.id, source.your_column, GETDATE())
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
-- 关键：用OUTPUT把变更写入日志表
OUTPUT 
    $action AS action_type,
    COALESCE(DELETED.id, INSERTED.id) AS target_id, -- 不管哪种操作都能拿到主键
    (SELECT DELETED.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS old_data, -- 旧数据转JSON
    (SELECT INSERTED.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS new_data -- 新数据转JSON
INTO data_warehouse.dbo.merge_change_log;

细节说明 #

• $action：直接告诉你这条变更是新增、更新还是删除，非常直观。
• DELETED和INSERTED：UPDATE操作时，两个表都有数据（旧值和新值）；INSERT只有INSERTED有数据；DELETE只有DELETED有数据。
• 用JSON存储全量数据：如果不想把每个字段都拆到日志表，用JSON打包旧/新数据是最省事的，后续要分析也方便解析。当然你也可以根据需求只记录关键字段，比如DELETED.last_modified, INSERTED.last_modified。
• 如果只是临时查看变更，不需要存到表：去掉INTO data_warehouse.dbo.merge_change_log这部分，执行MERGE后直接返回所有变更记录。

额外提醒 #

如果每日全量更新的数据量很大，建议给日志表加合适的索引（比如按change_time和target_id），避免后续查询变慢；另外，MERGE的OUTPUT子句不支持直接写入有触发器的表，这点需要注意。

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