Hello kev4king2000,
maybe like this:
BEGIN TRAN
DECLARE @ETL_HISTORY TABLE(
[Action] [varchar](50) NULL,
[New_FirstName] [nvarchar](50) NULL,
[New_MiddleName] [nvarchar](50) NULL,
[New_LastName] [nvarchar](50) NULL,
[New_EmailAddress] [nvarchar](50) NULL,
[Old_FirstName] [nvarchar](50) NULL,
[Old_MiddleName] [nvarchar](50) NULL,
[Old_LastName] [nvarchar](50) NULL,
[Old_EmailAddress] [nvarchar](50) NULL)--,
MERGE DBO.Contact T
USING Person.Contact S
ON T.EmailAddress = S.EmailAddress
WHEN MATCHED AND
(
T.FirstName <> S.FirstName OR
T.MiddleName <> S.MiddleName OR
T.LastName <> S.LastName
)
THEN UPDATE
SET
T.FirstName = S.FirstName,
T.MiddleName = S.MiddleName,
T.LastName = S.LastName
WHEN NOT MATCHED THEN INSERT
(
FirstName,
MiddleName,
LastName
)
VALUES
(
S.FirstName,
S.MiddleName,
S.LastName
)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT $action, inserted.*, deleted.* INTO @ETL_HISTORY;
SELECT * FROM @ETL_HISTORY
ROLLBACK TRAN
Kindest regards