使用INSERT实现SCD2时为何无法过滤compositekey为NULL的行？

原SQL的核心问题 #

1. 未过滤源表中compositekey为NULL的行，直接导致无效数据被插入
2. NOT EXISTS子句的条件逻辑错误，仅判断了双方compositekey都为NULL的极端情况，无法有效避免重复插入已存在的有效行

修正后的INSERT语句（仅插入有效新行） #

INSERT INTO target
SELECT s.ID, s.namn, s.hash, s.compositekey
FROM source s
-- 强制过滤源表中compositekey为空的无效行
WHERE s.compositekey IS NOT NULL
-- 判断目标表中不存在相同ID和compositekey的行，避免重复插入
AND NOT EXISTS (
    SELECT 1 FROM target t
    WHERE t.id = s.id 
    AND t.compositekey = s.compositekey
)

补充SCD2完整逻辑（含旧行过期更新） #

如果要完整实现SCD2，需要先将目标表中同一ID下的旧有效行标记为过期，再插入新行：

-- 步骤1：更新旧行，标记为过期（需确保目标表有end_date、is_current字段）
UPDATE target t
SET t.end_date = CURRENT_DATE,
    t.is_current = 0
FROM source s
WHERE t.id = s.id
AND t.is_current = 1
AND s.compositekey IS NOT NULL
AND t.compositekey != s.compositekey;

-- 步骤2：插入新的有效行
INSERT INTO target (ID, namn, hash, compositekey, start_date, is_current)
SELECT s.ID, s.namn, s.hash, s.compositekey, CURRENT_DATE, 1
FROM source s
WHERE s.compositekey IS NOT NULL
AND NOT EXISTS (
    SELECT 1 FROM target t
    WHERE t.id = s.id 
    AND t.compositekey = s.compositekey
    AND t.is_current = 1
);

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