I have 2 tables one containing historical data using type 2 SCD (Slowly changing dimensions) called DimBrand and another containing just the latest dimension data called LatestDimBrand. Using the merge function I will insert new records from LatestDimBrand into DimBrand, I will archive (apply an end date) to any DimBrand records which do not appear in the latest data, and finally enter a new record and archive the old record for any Brands which may have changed.
DimBrand (the target of our Inserts, Updates, Deletes) and DimLatestBrand (the source for Inserts,Updates,Deletes):
Here is complete merge statement;
INSERT #DimBrand ([BrandCode],[BrandName],[StartDate])
SELECT [BrandCode],[BrandName],getdate()
FROM (
MERGE #DimBrand AS Target
USING (
SELECT [BrandCode],[BrandName],[StartDate],[EndDate]
FROM #LatestDimBrand
) AS Source
ON (Target.[BrandCode] = Source.[BrandCode])
-------------------------------
WHEN MATCHED AND Target.[BrandName] <> Source.[BrandName]
THEN
UPDATE SET Target.[EndDate] = getdate()
-------------------------------
WHEN NOT MATCHED BY TARGET
THEN
INSERT (
[BrandCode]
,[BrandName]
,[StartDate]
,[EndDate]
)
VALUES (
Source.[BrandCode],
Source.[BrandName],
Source.[StartDate],
Source.[EndDate]
)
-------------------------------
WHEN NOT MATCHED BY SOURCE
THEN
UPDATE SET Target.[EndDate] = getdate()
-------------------------------
OUTPUT $Action, Source.*
) As i([Action],[BrandCode],[BrandName],[StartDate],[EndDate])
-------------------------------
WHERE [Action] = 'UPDATE'
AND BrandCode IS NOT NULLResources
https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15
Add to favorites
