I am trying to implement SCD type 2 using MERGE clause in SQL SERVER. I have written the following code:
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,
NULLIF(Name, '') Name,
NULLIF(Price_Level, '') 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 (
ISNULL(T.Customer_Name, '') <> ISNULL(S.Name, '')
OR ISNULL(T.Price_Level, '') <> ISNULL(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';
For few records it is detecting the changes, but instead of updating with the new value it is inserting the same values again and marking Is_active = 1.
Staging has everything in VARCHAR.
Price_Level has values like following:
Tier 3 (38% Discount)
Tier 4 (35% Discount) etc.
I tried to implement SCD type 2 using MERGE clause, but it is not working for few records.