使用SQL Server MERGE实现SCD Type 2时未插入最新值问题

你的SCD Type 2实现出现部分记录重复插入相同值的问题，核心原因是变更检测的比较逻辑存在漏洞：要么是字符串含前后空格、不可见字符导致视觉一致但实际判定为不同；要么是NULL与空字符串的处理规则不一致，导致无变更的记录被误判为有变更，触发旧记录标记为非活跃后插入了重复值。

具体修正步骤 #

1. 统一字符串处理，消除空格干扰 #

Staging表字段为VARCHAR类型，容易出现前后空格，需在比较和取值时添加LTRIM(RTRIM())处理，确保视觉相同的字符串能被正确判定为相等。

2. 规范NULL与空字符串的转换逻辑 #

将源表的空字符串统一转换为NULL，同时保证比较时两端的NULL处理规则一致，避免因转换差异导致误判。

3. 修正后的完整代码 #

INSERT INTO Dim_Customer (Customer_Internal_ID, Customer_Name,
                          Price_Level, INS_TS, Is_active)
SELECT
    Internal_ID, Name,
    Price_Level, GETDATE(), 1
FROM
    (MERGE Dim_Customer AS T 
     USING (SELECT
                Internal_ID, 
                -- 统一清理空格并转换空字符串为NULL
                NULLIF(LTRIM(RTRIM(Name)), '') AS Name,
                NULLIF(LTRIM(RTRIM(Price_Level)), '') AS Price_Level
            FROM
                Stg_Customer) AS S 
     ON T.Customer_Internal_ID = S.Internal_ID
     WHEN NOT MATCHED THEN
         INSERT (Customer_Internal_ID, Customer_Name,
                 Price_Level, INS_TS, Is_active)
         VALUES (S.Internal_ID, S.Name,
                 S.Price_Level, GETDATE(), 1)

     WHEN MATCHED AND T.Is_active = 1
                  AND (
                      -- 先清理空格再处理NULL，确保比较逻辑严谨
                      ISNULL(LTRIM(RTRIM(T.Customer_Name)), '') <> ISNULL(LTRIM(RTRIM(S.Name)), '')
                      OR ISNULL(LTRIM(RTRIM(T.Price_Level)), '') <> ISNULL(LTRIM(RTRIM(S.Price_Level)), '')
                  ) 
         THEN
             UPDATE
             SET T.Is_active = 0 
             OUTPUT S.Internal_ID, S.Name, S.Price_Level, $ACTION AS Merge_Action
    ) MRG
WHERE
    MRG.Merge_Action = 'UPDATE';

额外检查项 #

• 确认Dim_Customer与Stg_Customer的对应字段（如Customer_Name、Price_Level）VARCHAR长度一致，避免数据截断导致的隐性差异。
• 若Price_Level存在换行、制表符等不可见字符，可添加REPLACE函数进一步清理，例如REPLACE(REPLACE(Price_Level, CHAR(9), ''), CHAR(10), '')。

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