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';