Scenario :
Let's say we have dbo.Customer Table in OLTP Database or in Staging Database from which we have to load our Dim.Customer Slowly changing type 2 dimension by using TSQL Merge Statement.Solution:
Step 1:
Create Source Table dbo.Customer and insert couple of records.
USE TEST1 GO CREATE TABLE dbo.Customer ( CustomerID INT IDENTITY(1, 1), CustomerCode UNIQUEIDENTIFIER, FirstName VARCHAR(50), LastName VARCHAR(50), [Address] VARCHAR(200), Phone VARCHAR(10) )
INSERT INTO dbo.Customer
VALUES ( NEWID(),'Aamir','Shahzad',' 2119 XYZ NEW YORK USA','5054141969')
INSERT INTO dbo.Customer
VALUES ( NEWID(),'Najaf1','Ali',' 32211 XYZ NEW Jersey USA','5054141969')
Step 2:
Create Dimension Table. We used CustomerCode as business key from source.
CREATE TABLE Dim.Customer ( DimCustomerID INT IDENTITY(1, 1), CustomerCode UNIQUEIDENTIFIER, FirstName VARCHAR(50), LastName VARCHAR(50), [Address] VARCHAR(200), Phone VARCHAR(10), StartDT DATETIME, EndDT DATETIME, IsLatest BIT )
Step 3:
Create Merge Statement, the statement can be used in SQL Server agent job or It can be used in SSIS Package ( Execute SQL Task). If you wish , you can create stored procedure for this statement and use in SQL Server Agent or SSIS Package to populate Dim.Customer Table.
-- Insert Records from Inner Merge as they they are update and
-- need to insert as new record
INSERT INTO TestDB.Dim.Customer (CustomerCode, FirstName, LastName,
[Address], Phone, IsLatest,StartDT)
SELECT CustomerCode, FirstName, LastName,[Address], Phone,1,GETDATE() AS StartDT FROM(
-- Start of Merge Statement
MERGE INTO TestDB.Dim.Customer AS DST USING Test1.dbo.Customer AS SRC
ON (SRC.CustomerCode = DST.CustomerCode)
-- Business Key for Matching
-- WHEN Business Key does not match with Destination Records, Then insert
WHEN NOT MATCHED THEN INSERT (CustomerCode, FirstName, LastName, [Address], Phone, IsLatest,StartDT)
VALUES (SRC.CustomerCode, SRC.FirstName, SRC.LastName,
SRC.[Address],SRC.Phone, 1,GETDATE())
-- Business Key Matched but One or More than One filed has changed in Source)
WHEN MATCHED AND IsLatest = 1 AND (
-- Comparing if any of the field is different from
--Source in Destination WHEN Business Key matches. ISNULL(DST.FirstName,'') != ISNULL(SRC.FirstName,'') OR ISNULL(DST.LastName,'') != ISNULL(SRC.LastName,'') OR ISNULL(DST.Address,'') !=ISNULL(SRC.Address,'') OR ISNULL(DST.Phone,'') != ISNULL(SRC.Phone,'') )
--UPDATE the record. Expire the record by setting IsLatest=0 and EndDate=getdate()
THEN UPDATE SET DST.IsLatest = 0,
DST.EndDT = GETDATE()
-- Use the Source records which needs to be inserted in Dim because of Update.
OUTPUT SRC.CustomerCode, SRC.FirstName, SRC.LastName, SRC.[Address],SRC.phone,
$Action AS MergeAction) AS MRG
WHERE MRG.MergeAction = 'UPDATE';
Note:
I have used StartDT and EndDT as datetime, most of the time the dimensions are loaded once a day and these columns can be changed to date datatype.