1

I am trying to create a SCD type 2 table in SQL server for a data warehouse. I am using the MERGE statement, and my problem is with the "When Matched" part. For example: I have a certain customer that moved from Miami to New York. I would like to save the historic version and insert a new record for that customer. The problem is that this is not working.

Note that the "When Not Matched" part is working great and new customers are inserted. Did anyone else encounter this problem? Thank you!

This is the code I wrote:

insert FandangoDW.dbo.DIM_CUSTOMERS
        select Customer,
              CustomerName,
              Gender ,
              BirthDate,
              Age,
              AgeGroup,
              CustomerCity,
              CustomerState,
              CustomerCountry,
              ValidFrom,
              ValidUntil
        from (merge FandangoDW.dbo.DIM_CUSTOMERS as dim using (select * from FandangoSTG.dbo.STG_CUSTOMERS) as stg 
            on dim.Customer = stg.Customer 
            WHEN NOT MATCHED by target
            THEN
            INSERT
            (
              Customer ,
              CustomerName,
              Gender ,
              BirthDate,
              Age,
              AgeGroup,
              CustomerCity,
              CustomerState,
              CustomerCountry,
              ValidFrom,
              ValidUntil
            )
            VALUES
            ( stg.Customer ,
              stg.CustomerName,
              stg.Gender ,
              stg.BirthDate,
              stg.Age,
              stg.AgeGroup,
              stg.CustomerCity,
              stg.CustomerState,
              stg.CustomerCountry,
              GETDATE() - 1,
              null
            )
            WHEN MATCHED AND dim.ValidUntil = null AND stg.CustomerCity <> dim.CustomerCity
            THEN
            UPDATE  SET
            dim.ValidUntil = GETDATE() - 2,
            dim.CustomerCity = stg.CustomerCity,
            dim.CustomerState=stg.CustomerState,
            dim.CustomerCountry=stg.CustomerCountry
            OUTPUT
            $Action,
            stg.Customer ,
            stg.CustomerName,
            stg.Gender ,
            stg.BirthDate,
            stg.Age,
            stg.AgeGroup,
            stg.CustomerCity,
            stg.CustomerState,
            stg.CustomerCountry,
            GETDATE() - 1,
            null
)as merge_out
(
    Action,
    Customer,
    CustomerName,
    Gender,
    BirthDate,
    Age,
    AgeGroup,
    CustomerCity,
    CustomerState,
    CustomerCountry,
    ValidFrom,
    ValidUntil
)
WHERE  Action = 'UPDATE';
  • What exactly isn't working? Although I must say this code isn't going to work if the target table has foreign keys (you would need an intermediate table variable for the `OUTPUT`), it's also strange that you seem to be inserting the same values twice. Personally I would use triggers to enforce Type 2 SCD – Charlieface Jul 21 '21 at 11:56
  • hi @Charlieface, the table has no foreign keys. – Tal Cordova Jul 21 '21 at 13:09
  • The part of when matched is not working. Even though the CustomerCity is different, the ValidUntil field does not update, and a new record, which is the new version, is not inserted to the table. – Tal Cordova Jul 21 '21 at 13:18
  • `dim.ValidUntil = null` should be `dim.ValidUntil is null`. And if `CustomerCity` can be null then you also need `EXISTS (SELECT stg.CustomerCity EXCEPT SELECT dim.CustomerCity)` – Charlieface Jul 21 '21 at 20:36
  • @Charlieface it did! Thank you very much! – Tal Cordova Jul 22 '21 at 05:14

0 Answers0