SCD Type 2实现求助：MERGE语句重复插入已存在记录

嗨，我来帮你捋捋这个问题~ 你现在想用MERGE实现SCD Type 2，把staging表的记录同步到目标表，但遇到了已存在的记录被重复插入的情况，对吧？

先拆解下你当前的MERGE逻辑，核心问题出在source子查询的判断条件以及MERGE的匹配逻辑上：

问题分析 #

你的source子查询第二部分用了NOT EXISTS (SELECT 1 FROM employee1 e WHERE e.EMPID = s.id AND e.IS_ACTIVE = 'Y' AND e.SALARY = s.SALARY)，这个条件只检查活跃的目标记录是否有相同EMPID和工资，但如果目标表中存在非活跃的同EMPID同工资记录，或者你想避免的是“只要EMPID存在就不重复插入（不管是否活跃）”，这个条件就会漏判，导致不该插入的记录被重复插入。

另外，你的MERGE匹配条件是target.surrogate_key = source.surrogate_key，这个只能匹配到需要被标记为非活跃的旧记录，对于新插入的记录（surrogate_key为NULL），只会走WHEN NOT MATCHED分支，但如果你的需求是“当staging表的记录和目标表中任何一条（不管活跃与否）完全相同时，不插入”，当前逻辑也没覆盖到这种场景。

修正方案 #

针对SCD Type 2的标准需求（属性变化时归档旧记录、插入新记录；新EMPID插入；完全重复的活跃记录不插入），我们可以调整source子查询和MERGE逻辑：

MERGE INTO employee1 target
USING (
    -- 第一部分：找出需要归档的旧活跃记录（属性发生变化的情况）
    SELECT 
        e.surrogate_key,
        s.id,
        s.name,
        s.salary,
        s.START_DATE,
        'UPDATE' AS action_type
    FROM employee1 e
    JOIN stg_employee1 s 
        ON e.EMPID = s.id
    WHERE e.IS_ACTIVE = 'Y'
      -- 检查所有关键属性是否变化，按需调整字段
      AND (e.name <> s.name OR e.SALARY <> s.SALARY OR e.START_DATE <> s.START_DATE)
    
    UNION ALL
    
    -- 第二部分：找出需要插入的新记录（EMPID不存在，或存在但属性不同且无相同的活跃记录）
    SELECT 
        NULL AS surrogate_key,
        s.id,
        s.name,
        s.SALARY,
        s.START_DATE,
        'INSERT' AS action_type
    FROM stg_employee1 s
    WHERE NOT EXISTS (
        SELECT 1 FROM employee1 e 
        WHERE e.EMPID = s.id 
          AND e.name = s.name 
          AND e.SALARY = s.SALARY 
          AND e.START_DATE = s.START_DATE
          AND e.IS_ACTIVE = 'Y' -- 只确保没有相同的活跃记录，历史归档记录不影响SCD Type 2逻辑
    )
) source
ON (target.surrogate_key = source.surrogate_key)
WHEN MATCHED THEN
  UPDATE SET target.end_date = source.START_DATE - 1, target.IS_ACTIVE = 'N'
WHEN NOT MATCHED AND source.action_type = 'INSERT' THEN
  INSERT (EMPID, name, SALARY, start_date, end_date, IS_ACTIVE)
  VALUES (source.id, source.name, source.SALARY, source.START_DATE, TO_DATE('31-DEC-99', 'DD-MON-RR'), 'Y');

关键调整点 #

1. 属性变化检查：把单一的工资检查改成所有关键业务属性（name、salary、start_date）的变化检查，确保任何属性变更都能触发旧记录的归档操作。
2. 插入条件优化：第二部分的NOT EXISTS条件，只检查目标表中是否有相同EMPID且所有属性一致的活跃记录，既符合SCD Type 2保留历史记录的要求，又不会重复插入完全相同的活跃数据。
3. 新增action_type标记：明确区分更新和插入的数据源，避免不必要的插入触发，逻辑更清晰。

如果你的需求是“只要EMPID存在，不管属性是否相同都不插入”（这其实偏离了标准SCD Type 2的设计），可以把第二部分的NOT EXISTS条件改成NOT EXISTS (SELECT 1 FROM employee1 e WHERE e.EMPID = s.id)，但这样就失去了跟踪属性变化的能力，你可以根据实际业务场景选择。

备注：内容来源于stack exchange，提问作者srikanth b