Here the script
CREATE TABLE Persons
(
ID int IDENTITY PRIMARY KEY
,FirstName varchar(20)
,LastName varchar (20)
,Title varchar(10)
,IsRowCurrent tinyint
,LastUpdated DATE
,ValidFrom DATE
,ValidTo DATE
)
--Populate Persons table with existing customers
INSERT INTO Persons
VALUES ('Anna','Kournikova','Miss',1,'2012-11-01','2012-11-01','9999-12-31')
,('Roger','Federer','Mr',1,'2012-11-01','2012-11-01','9999-12-31')
GO
--Create an update table to hold new/updated persons
CREATE TABLE NewPersons
(
FirstName varchar (20)
,LastName varchar (20)
,Title varchar(10)
)
--Populate People table with existing people
INSERT INTO NewPersons
VALUES ('Anna','Smith','Mrs') --Update
,('Rafael','Nadal','Mr') --New entry
GO
---------------------------------------------------------------------------
-- Mixed Merge - Type 1 and 2
INSERT INTO Persons
( FirstName
,LastName
,Title
,IsRowCurrent
,LastUpdated
,ValidFrom
,ValidTo
) SELECT
FirstName
,LastName
,Title
,1 --IsRowCurrent
,GETDATE() --LastUpdated
,GETDATE() --ValidFrom
,'9999-12-31' --ValidTo
FROM (
MERGE INTO Persons AS [Target]
USING NewPersons AS [Source]
ON Target.FirstName = Source.FirstName
WHEN MATCHED and Target.IsRowCurrent = 1 -- SCD 1/2 changes
AND
(
Target.FirstName <> Source.FirstName
OR Target.LastName <> Source.LastName
OR Target.Title <> Source.Title
)
THEN UPDATE SET
Target.IsRowCurrent = 0 --Obsolete
,Target.LastUpdated = GETDATE()
,Target.ValidTo = GETDATE()
WHEN NOT MATCHED -- New entries
THEN INSERT (
FirstName
,LastName
,Title
,IsRowCurrent
,LastUpdated
,ValidFrom
,ValidTo
)
Values (
Source.FirstName
,Source.LastName
,Source.Title
,1 --IsRowCurrent
,GETDATE() --LastUpdated
,GETDATE() --ValidFrom
,'9999-12-31' --ValidTo
)
WHEN NOT MATCHED BY SOURCE -- Obsolete removed entries
AND Target.IsRowCurrent = 1
THEN UPDATE SET
Target.IsRowCurrent = 0 --Removed
,Target.LastUpdated = GETDATE()
,Target.ValidTo = GETDATE()
OUTPUT $action AS Action
,[Source].*
) AS MergeOutput
WHERE MergeOutput.Action = 'UPDATE'
AND FirstName IS NOT NULL;
If Anna is removed and afterwards she is entyered again, she still appears as unactive (IsRowCurrent=0)...