ok, lets say that the script look like this:
USE master
GO
--Create the main Persons table
CREATE TABLE Persons
(
FirstName varchar(20)
,LastName varchar (20)
,Title varchar(10)
,Email varchar (20)
,IsRowCurrent tinyint
,LastUpdated DATETIME
,ValidFrom DATETIME
,ValidTo DATETIME
)
--Populate Persons table with existing customers
INSERT INTO Persons
VALUES ('Anna','Kournikova','Miss','anna@emailme.com',1,'2012-11-01','2012-11-01','9999-12-31')
,('Roger','Federer','Mr','roger@emailme.com',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)
,Email varchar (20)
)
--Populate People table with existing people
INSERT INTO NewPersons
VALUES ('Anna','Smith','Mrs','anna@emailme.com') --Update
,('Rafael','Nadal','Mr','roger@emailme.com') --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.Email = Source.Email
WHEN MATCHED and Target.IsRowCurrent = 1 -- SCD 1/2 changes
AND
(
Target.FirstName <> Source.LastName
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
,Source.Email
,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;