Breaking News: Grepper is joining You.com. Read the official announcement!

How to Load Slowly Changing Dimension Type 2 ( SCD2) By Using TSQL Merge Statement

Thokozani Zulu answered on October 30, 2022 Popularity 1/10 Helpfulness 1/10

answer How to Load Slowly Changing Dimension Type 2 ( SCD2) By Using TSQL Merge Statement

How to Load Slowly Changing Dimension Type 2 ( SCD2) By Using TSQL Merge Statement

Comment

-- Define the dates used in validity - assume whole 24 hour cycles
DECLARE @Yesterday INT = (YEAR(DATEADD(dd,-1,GETDATE())) * 10000) + (MONTH(DATEADD(dd,-1,GETDATE())) * 100) + DAY(DATEADD(dd,-1,GETDATE()))
DECLARE @Today INT = (YEAR(GETDATE()) * 10000) + (MONTH(GETDATE()) * 100) + DAY(GETDATE())
-- Outer insert - the updated records are added to the SCD2 table
INSERT INTO dbo.Client_SCD2 (BusinessKey, ClientName, Country, Town, Address1, Address2, ClientType, ClientSize, ValidFrom, IsCurrent)
SELECT ID, ClientName, Country, Town, Address1, Address2, ClientType, ClientSize, @Today, 1
FROM
(
-- Merge statement
MERGE INTO dbo.Client_SCD2 AS DST
USING CarSales.dbo.Client AS SRC
ON (SRC.ID = DST.BusinessKey)
-- New records inserted
WHEN NOT MATCHED THEN 
INSERT (BusinessKey, ClientName, Country, Town, County, Address1, Address2, ClientType, ClientSize, ValidFrom, IsCurrent)
VALUES (SRC.ID, SRC.ClientName, SRC.Country, SRC.Town, SRC.County, Address1, Address2, ClientType, ClientSize, @Today, 1)
-- Existing records updated if data changes
WHEN MATCHED 
AND IsCurrent = 1
AND (
 ISNULL(DST.ClientName,'') <> ISNULL(SRC.ClientName,'') 
 OR ISNULL(DST.Country,'') <> ISNULL(SRC.Country,'') 
 OR ISNULL(DST.Town,'') <> ISNULL(SRC.Town,'')
 OR ISNULL(DST.Address1,'') <> ISNULL(SRC.Address1,'')
 OR ISNULL(DST.Address2,'') <> ISNULL(SRC.Address2,'')
 OR ISNULL(DST.ClientType,'') <> ISNULL(SRC.ClientType,'')
 OR ISNULL(DST.ClientSize,'') <> ISNULL(SRC.ClientSize,'')
 )
-- Update statement for a changed dimension record, to flag as no longer active
THEN UPDATE 
SET DST.IsCurrent = 0, DST.ValidTo = @Yesterday
OUTPUT SRC.ID, SRC.ClientName, SRC.Country, SRC.Town, SRC.Address1, SRC.Address2, SRC.ClientType, SRC.ClientSize, $Action AS MergeAction
) AS MRG
WHERE MRG.MergeAction = 'UPDATE'
;

Popularity 1/10 Helpfulness 1/10 Language typescript

Source: www.sqlservercentral.com

Tags: dimension load typescript using

Link to this answer
Share Copy Link

Contributed on Oct 30 2022

Thokozani Zulu

0 Answers Avg Quality 2/10

How to Load Slowly Changing Dimension Type 2 ( SCD2) By Using TSQL Merge Statement

Contents

More Related Answers

How to Load Slowly Changing Dimension Type 2 ( SCD2) By Using TSQL Merge Statement

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.