merge2.sql

/*************************************************************
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)
;