Skip to content

Instantly share code, notes, and snippets.

@tdmitch tdmitch/merge2.sql
Created Jun 1, 2018

Embed
What would you like to do?
/*************************************************************
The MERGE statement updates the DimEmp table
*************************************************************/
MERGE [dw].[DimEmp] AS target
USING (
-- Here is the original source query, now wrapped in a MERGE statement
SELECT ct.ID
, e.FirstName
, e.MiddleName
, e.LastName
, ct.SYS_CHANGE_OPERATION [Operation]
FROM CHANGETABLE(CHANGES dbo.Emp, @StartVersionID) ct
LEFT JOIN dbo.Emp e
ON e.ID = ct.ID
WHERE (SELECT MAX(v) FROM (VALUES(ct.SYS_CHANGE_VERSION), (ct.SYS_CHANGE_CREATION_VERSION)) AS VALUE(v)) <= @EndVersionID
) AS source
-- Join on business key
ON source.ID = target.EmpID
-- Handle updated and deleted records
WHEN MATCHED THEN
UPDATE
SET target.FirstName = CASE WHEN SOURCE.Operation = 'U' THEN source.FirstName ELSE target.FirstName END
, target.MiddleName = CASE WHEN SOURCE.Operation = 'U' THEN source.MiddleName ELSE target.MiddleName END
, target.LastName = CASE WHEN SOURCE.Operation = 'U' THEN source.LastName ELSE target.LastName END
, target.Active = CASE WHEN source.Operation = 'D' THEN 0 ELSE 1 END -- Set inactive for deletes only
-- Handle inserted records
WHEN NOT MATCHED BY TARGET
THEN INSERT (EmpID, FirstName, MiddleName, LastName, UpdatedDate, Active)
VALUES (source.ID, source.FirstName, source.MiddleName, source.LastName, GETDATE(), 1)
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.