CREATE DATABASE TEST
GO
USE [TEST]
GO
/*
Msg 8672, Level 16, State 1, Line 34
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

This means that we will have to perform a merge inserting into staging as well as a merge inserting from staging into repository
*/
CREATE TABLE Test1 (ID INT, SomeText1 CHAR(1), SomeText2 CHAR(1))
CREATE TABLE Test2 (ID INT, SomeText1 CHAR(1), SomeText2 CHAR(1), SomeText3 CHAR(1))
CREATE TABLE StagingTest1 (ID INT, SomeText1 CHAR(1), SomeText2 CHAR(1))
CREATE TABLE StagingTest2 (ID INT, SomeText1 CHAR(1), SomeText2 CHAR(1), SomeText3 CHAR(1))
CREATE TABLE TestDelta (ID INT, TableName VARCHAR(50), FieldChanged VARCHAR(20), OldValue VARCHAR(20), NewValue VARCHAR(20), UpdatedDateTime DATETIME)

INSERT INTO StagingTest1 (ID,SomeText1, SomeText2)
SELECT 4, 'f','a'
UNION ALL 
SELECT 2, 'b','b'
GO
INSERT INTO StagingTest2 (ID,SomeText1, SomeText2, SomeText3)
SELECT 24, 'e','a','1'
UNION ALL 
SELECT 20, 'a','w','2'
UNION ALL 
SELECT 25, 'a','x','2'
GO
INSERT INTO Test1 (ID,SomeText1, SomeText2)
SELECT 1, 'a','a'
UNION ALL 
SELECT 2, 'b','a'
UNION ALL
SELECT 3, 'c','a'
UNION ALL 
SELECT 4, 'd','a'
GO
INSERT INTO Test2 (ID,SomeText1, SomeText2, SomeText3)
SELECT 20, 'a','z','1'
UNION ALL 
SELECT 21, 'b','z','1'
UNION ALL
SELECT 22, 'c','z','1'
UNION ALL 
SELECT 23, 'd','z','1'
GO
--SELECT * FROM StagingTest1
--SELECT * FROM Test1
GO
CREATE TABLE #Test1
    (ID1 int,
     OldValue1 CHAR(1),
     OldValue2 CHAR(1),
	 [Action] nvarchar(10),
	 ID2 int,
     NewValue1 CHAR(1),
     NewValue2 CHAR(1),
     ActionDateTime datetime
    );
    CREATE TABLE #Test2
    (ID1 int,
     OldValue1 CHAR(1),
     OldValue2 CHAR(1),
     OldValue3 CHAR(1),
     [Action] nvarchar(10),
	 ID2 int,
     NewValue1 CHAR(1),
     NewValue2 CHAR(1),
     NewValue3 CHAR(1),
     ActionDateTime datetime
    );

MERGE Test1 AS Target
USING (SELECT ID, SomeText1, SomeText2 FROM StagingTest1) AS Source (ID, SomeText1, SomeText2)
ON (Target.ID = Source.ID)
WHEN MATCHED THEN
UPDATE SET SomeText1 = Source.SomeText1, SomeText2 = Source.SomeText2
WHEN NOT MATCHED THEN 
INSERT (ID, SomeText1, SomeText2)
VALUES(Source.ID, Source.SomeText1, Source.SomeText2)
OUTPUT deleted.*, $action, inserted.*, CURRENT_TIMESTAMP INTO #Test1;

MERGE Test2 AS Target
USING (SELECT ID, SomeText1, SomeText2, SomeText3 FROM StagingTest2) AS Source (ID, SomeText1, SomeText2, SomeText3)
ON (Target.ID = Source.ID)
WHEN MATCHED THEN
UPDATE SET SomeText1 = Source.SomeText1, SomeText2 = Source.SomeText2, SomeText3 = Source.SomeText3
WHEN NOT MATCHED THEN 
INSERT (ID, SomeText1, SomeText2, SomeText3)
VALUES(Source.ID, Source.SomeText1, Source.SomeText2, Source.SomeText3)
OUTPUT deleted.*, $action, inserted.*, CURRENT_TIMESTAMP INTO #Test2;

--SELECT * FROM #Test1
--SELECT * FROM #Test2

-- Insert into TestDelta from Test1
INSERT INTO TestDelta
SELECT ID1 AS ID
, 'Test1'
, CASE WHEN OldValue1 <> NewValue1 THEN 'SomeText1' END AS FieldName
, CASE WHEN OldValue1 <> NewValue1 THEN OldValue1 END AS OldValue
, CASE WHEN OldValue1 <> NewValue1 THEN NewValue1 END AS NewValue
, CURRENT_TIMESTAMP
FROM #Test1 WHERE [Action] = 'UPDATE'
AND OldValue1 <> NewValue1
UNION ALL
SELECT ID1 AS ID
, 'Test1'
, CASE WHEN OldValue2 <> NewValue2 THEN 'SomeText2' END AS FieldName
, CASE WHEN OldValue2 <> NewValue2 THEN OldValue2 END AS OldValue
, CASE WHEN OldValue2 <> NewValue2 THEN NewValue2 END AS NewValue
, CURRENT_TIMESTAMP
FROM #Test1 WHERE [Action] = 'UPDATE'
AND OldValue2 <> NewValue2

-- Insert into TestDelta from Test2
INSERT INTO TestDelta
SELECT ID1 AS ID
, 'Test2'
, CASE WHEN OldValue1 <> NewValue1 THEN 'SomeText1' END AS FieldName
, CASE WHEN OldValue1 <> NewValue1 THEN OldValue1 END AS OldValue
, CASE WHEN OldValue1 <> NewValue1 THEN NewValue1 END AS NewValue
, CURRENT_TIMESTAMP
FROM #Test2 WHERE [Action] = 'UPDATE'
AND OldValue1 <> NewValue1
UNION ALL
SELECT ID1 AS ID
, 'Test2'
, CASE WHEN OldValue2 <> NewValue2 THEN 'SomeText2' END AS FieldName
, CASE WHEN OldValue2 <> NewValue2 THEN OldValue2 END AS OldValue
, CASE WHEN OldValue2 <> NewValue2 THEN NewValue2 END AS NewValue
, CURRENT_TIMESTAMP
FROM #Test2 WHERE [Action] = 'UPDATE'
AND OldValue2 <> NewValue2
UNION ALL
SELECT ID1 AS ID
, 'Test2'
, CASE WHEN OldValue3 <> NewValue3 THEN 'SomeText3' END AS FieldName
, CASE WHEN OldValue3 <> NewValue3 THEN OldValue3 END AS OldValue
, CASE WHEN OldValue3 <> NewValue3 THEN NewValue3 END AS NewValue
, CURRENT_TIMESTAMP
FROM #Test2 WHERE [Action] = 'UPDATE'
AND OldValue3 <> NewValue3

DROP TABLE #Test1
DROP TABLE #Test2

SELECT * FROM TestDelta
GO

USE [master]
GO
DROP DATABASE [TEST]