SQL TRACKER: Merge Concept with Output Operation.

CREATE TABLE ReadOnlyTable (

Id INT PRIMARY KEY,

Col1 INT,

Col2 INT,

Timestamp DATETIME

)

CREATE TABLE HistoryTable (

Id INT PRIMARY KEY,

Col1 INT,

Col2 INT,

Timestamp DATETIME

)

INSERT INTO ReadOnlyTable (Id, Col1, Col2, Timestamp)

VALUES

(1, 1, 1, GETDATE()),

(2, 2, 2, GETDATE()),

(3, 3, 3, GETDATE())

Merge Concept:

MERGE INTO HistoryTable Dst

USING ReadOnlyTable Src ON Dst.id = Src.id

WHEN MATCHED AND (Dst.col1 <> Src.col1 OR Dst.col2 <> Src.col2 OR Dst.Timestamp <> Src.Timestamp) THEN

UPDATE

SET Dst.col1 = Src.col1, Dst.col2 = Src.col2, Dst.Timestamp = src.Timestamp

WHEN NOT MATCHED BY TARGET THEN

INSERT (id, col1, col2, Timestamp)

VALUES (Src.id, Src.col1, Src.col2, Src.Timestamp)

WHEN NOT MATCHED BY SOURCE THEN

DELETE

OUTPUT

GETDATE() AS ChangeDate,

COALESCE(Inserted.Id, Deleted.Id) AS Id,

CASE

WHEN Deleted.Id IS NULL AND Inserted.Id IS NOT NULL THEN 'i'

WHEN Deleted.Id IS NOT NULL AND Inserted.Id IS NOT NULL THEN 'u'

WHEN Deleted.Id IS NOT NULL AND Inserted.Id IS NULL THEN 'd'

END AS ChangeType,

deleted.col1 AS col1_before,

deleted.col2 AS col2_before,

deleted.Timestamp AS Timestamp_before,

inserted.col1 AS col1_after,

inserted.col2 AS col2_after,

inserted.Timestamp AS Timestamp_after;

Example For Insert,Update,Delete It will track with Output Process

UPDATE ReadOnlyTable SET Col1 = 42

WHERE Id = 1

DELETE FROM ReadOnlyTable

WHERE Id = 2

INSERT INTO ReadOnlyTable (Id, Col1, Col2, Timestamp)

VALUES (4, 4, 4, GETDATE())

Now We Track the Output So Create a new table

CREATE TABLE ReadOnlyTableChanges

(

ChangeId INT IDENTITY PRIMARY KEY,

ChangeDate DATETIME,

Id INT,

ChangeType CHAR(1),

Col1_before INT,

Col2_before INT,

Timestamp_before DATETIME,

Col1_after INT,

Col2_after INT,

Timestamp_after DATETIME

)

Merge Concept: Track Information Insert into Tracking Table