使用MERGE对比两表并插入带递增版本号的变更历史记录的实现方法

核心问题说明 #

你原有代码存在两个核心错误：

• 直接匹配ProductHistory全表数据，而非每个ID对应的最新版本记录，无法正确判断是否发生变更
• 对匹配到的历史记录执行UPDATE操作，违反了历史表仅新增、不可修改原有变更记录的设计要求

优化实现方案 #

不用强行使用MERGE语句，通过CTE筛选出所有需要插入的变更记录后直接插入即可，逻辑更直观易维护：

WITH LatestProductHistory AS (
    -- 提取每个产品ID对应的最新版本历史记录
    SELECT 
        ID, 
        ProductCode, 
        ProductType, 
        Margin, 
        Version,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Version DESC) AS rn
    FROM ProductHistory
),
NeedInsertRecords AS (
    SELECT
        s.ID,
        s.ProductCode,
        s.ProductType,
        s.Margin,
        -- 新增产品版本号为0，变更产品版本号为最新版本号+1
        ISNULL(l.Version + 1, 0) AS NewVersion
    FROM Product s
    LEFT JOIN LatestProductHistory l 
        ON s.ID = l.ID AND l.rn = 1
    -- 筛选条件：新增产品 或 任意字段发生变更
    WHERE 
        l.ID IS NULL 
        OR l.ProductCode <> s.ProductCode
        OR l.ProductType <> s.ProductType
        OR l.Margin <> s.Margin
)
-- 插入所有符合要求的变更记录
INSERT INTO ProductHistory (ID, ProductCode, ProductType, Margin, Version)
SELECT ID, ProductCode, ProductType, Margin, NewVersion
FROM NeedInsertRecords;

注意事项 #

• 如果你的表字段允许为NULL，直接用<>比较会无法识别NULL值的变更，需要调整比较逻辑，例如字符串字段可以写成ISNULL(l.ProductCode, '') <> ISNULL(s.ProductCode, '')，数值字段可以写成ISNULL(l.Margin, 0) <> ISNULL(s.Margin, 0)，默认值根据你的业务规则调整即可。
• 该方案支持批量处理新增、变更两种场景，不会修改任何已有的历史记录，完全符合历史表的设计要求。

可选优化 #

如果需要避免并发操作导致的版本号重复，可以给ProductHistory表添加联合唯一约束CONSTRAINT UQ_ProductHistory_ID_Version UNIQUE (ID, Version)，同时执行插入操作时添加合适的事务隔离级别或者锁提示即可。

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