使用Insert/Update实现SCD2时，Insert语句无法检测目标表已有行？

问题场景 #

要实现缓慢变化维度2（SCD2）：当源表与目标表ID匹配但哈希值（hashh）不同时，向目标表插入新行并设置enablee=1标记为当前版本，同时将旧版本的enablee设为0。当前写法重复执行会插入大量重复哈希值的行，不符合预期。

当前错误语句：

INSERT INTO target
    SELECT s.ID, s.namn, s.hashh, 1 AS enablee
    FROM source s
    JOIN target t ON s.id = t.id 
    WHERE s.hashh <> t.hashh

实际错误输出：

1   demo    222 0
1   demo    22220
1   demo    222 1
2   demo2   666 1
2   demo2   666 1
2   demo2   888  1

期望输出：

1   demo    222 1
1   demo    22220
2   demo2   666 1
2   demo2   888 0

问题原因 #

1. 未检查目标表是否已存在当前源数据的ID+hashh组合，导致重复执行时反复插入相同行
2. 缺少对旧版本数据的失效标记操作，没有将原enablee=1的行更新为0

解决方案 #

分两步执行（建议放在事务中保证原子性）：

1. 标记旧版本为失效 #

先将目标表中与源表ID匹配但哈希不同的当前生效行（enablee=1）设置为失效：

UPDATE target
SET enablee = 0
WHERE EXISTS (
    SELECT 1
    FROM source s
    WHERE s.id = target.id
      AND s.hashh <> target.hashh
      AND target.enablee = 1
)

2. 插入新版本（避免重复） #

插入源表中与目标表ID匹配但哈希不同，且目标表中尚未存在该ID+hashh组合的行：

INSERT INTO target (ID, namn, hashh, enablee)
SELECT s.ID, s.namn, s.hashh, 1 AS enablee
FROM source s
WHERE EXISTS (
    SELECT 1
    FROM target t
    WHERE t.id = s.id
      AND t.hashh <> s.hashh
)
AND NOT EXISTS (
    SELECT 1
    FROM target t
    WHERE t.id = s.id
      AND t.hashh = s.hashh
)

效果验证 #

执行上述两步后，目标表会呈现预期的版本状态。重复执行插入语句时，因为目标表已存在对应的ID+hashh组合，会返回(0 rows affected)，不会产生重复数据。

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