SQL Server使用Merge实现插入更新并标记无变更、删除行的方案

需求背景 #

需要在SQL Server中创建存储过程实现upsert功能，将staging临时表（源表）数据同步到最终目标表，每次新批次数据流入时，要准确标记出新增、更新、无变更、已删除四类行。

现有逻辑问题 #

原有基于Merge语法的实现，只要联合主键（first_name、last_name、dob）匹配成功，无论其他业务字段是否有变化，都会执行更新操作，统一标记为Updated状态，导致无变更行被错误标记，不符合数据管道的状态识别要求。

核心优化思路 #

1. 给Merge的WHEN MATCHED分支增加字段变更校验，仅当业务字段实际发生变化时才执行更新、标记为更新状态
2. 单独处理匹配成功但无变更的行，标记为无变更状态，同步更新处理时间避免被误判为删除
3. 优化删除逻辑，同步更新删除行的处理时间，方便后续批次的状态判断

优化后存储过程代码 #

CREATE PROCEDURE [dbo].[upsert_with_flag_2]
AS
    DECLARE @current_time AS datetime
    SET @current_time = GETDATE()

    MERGE [dbo].[employee] AS Target
    USING [dbo].[employee_staging] AS Source
          ON Source.[first_name] = Target.[first_name] 
             AND Source.[last_name] = Target.[last_name] 
             AND Source.[dob] = Target.[dob]

    -- 仅业务字段发生变更时才执行更新，标记为Updated
    WHEN MATCHED AND (
        ISNULL(Target.[salary],0) <> ISNULL(Source.[salary],0)
        OR ISNULL(Target.[current_address],'') <> ISNULL(Source.[current_address],'')
    )
        THEN
            UPDATE 
            SET Target.[salary] = Source.[salary],
                Target.[current_address] = Source.[current_address],
                Target.[is_deleted] = 'Updated',
                Target.[processed_date] = @current_time

    -- 目标表不存在的新行执行插入，标记为New
    WHEN NOT MATCHED BY Target 
        THEN
            INSERT ([first_name], [last_name], 
                    [dob], [salary], 
                    [current_address], [is_deleted], 
                    [processed_date])
            VALUES (Source.[first_name], Source.[last_name],
                    Source.[dob], Source.[salary], 
                    Source.[current_address], 'New',
                    @current_time);

    -- 处理匹配成功但无变更的行，标记为Existing，更新处理时间避免被误删
    UPDATE Target
    SET Target.[is_deleted] = 'Existing',
        Target.[processed_date] = @current_time
    FROM [dbo].[employee] AS Target
    INNER JOIN [dbo].[employee_staging] AS Source
        ON Source.[first_name] = Target.[first_name] 
        AND Source.[last_name] = Target.[last_name] 
        AND Source.[dob] = Target.[dob]
    WHERE 
        ISNULL(Target.[salary],0) = ISNULL(Source.[salary],0)
        AND ISNULL(Target.[current_address],'') = ISNULL(Source.[current_address],'')
        AND Target.[processed_date] < @current_time;

    -- 标记本次批次未出现的历史有效行为已删除，同步更新处理时间
    UPDATE [dbo].[employee]
    SET [is_deleted] = 'deleted',
        [processed_date] = @current_time
    WHERE ([is_deleted] IN ('New','Updated','Existing')) 
      AND [processed_date] < @current_time

注意事项 #

• 代码中使用ISNULL做兼容处理是为了避免字段为NULL时的等值判断错误，可根据实际业务的字段非空规则调整
• 联合主键（first_name、last_name、dob）需要确保全局唯一，否则会触发Merge匹配到多行的报错
• 大数据量场景下，建议给源表和目标表的联合匹配键建立索引，大幅提升Merge执行效率

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