I created the following MERGE
statement and it is working as expected.
Unfortunately, I need to modify this merge statement due to new requirement. Before inserting/updating/deleting via MERGE
statement, I need to capture rows into a third table. Once reviewed by users, I can then push my changes to TARGET table.
Is this possible with the MERGE
statement?
MERGE INTO LARBI_Dim AS TARGET
USING (SELECT *
FROM #LARBI_Dim_view) AS SOURCE ON SOURCE.RefLeaseAssumptionID = TARGET.RefLeaseAssumptionID
WHEN NOT MATCHED BY TARGET
THEN
INSERT ([RefLeaseAssumptionID], isLinkedFlag, isExtendedFlag)
VALUES (SOURCE.[RefLeaseAssumptionID], SOURCE.isLinkedFlag, SOURCE.isExtendedFlag)
WHEN MATCHED AND
(SOURCE.isLinkedFlag <> TARGET.isLinkedFlag
OR SOURCE.isExtendedFlag <> TARGET.isExtendedFlag)
THEN
UPDATE
SET TARGET.isLinkedFlag = SOURCE.isLinkedFlag,
TARGET.isExtendedFlag = SOURCE.isExtendedFlag
WHEN NOT MATCHED BY SOURCE
THEN
DELETE
OUTPUT $ACTION,
getdate() as 'ChangedDate',
Isnull(inserted.[RefLeaseAssumptionID], deleted.[RefLeaseAssumptionID]) as 'RefLeaseAssumptionID',
CASE WHEN $ACTION = 'UPDATE' AND inserted.isLinkedFlag <> deleted.isLinkedFlag
THEN cast(deleted.isLinkedFlag as varchar(30)) + '-->' + cast(inserted.isLinkedFlag as varchar(30))
END as 'isLinkedFlag',
CASE WHEN $ACTION = 'UPDATE' AND inserted.isExtendedFlag <> deleted.isExtendedFlag
THEN cast(deleted.isExtendedFlag as varchar(30)) + '-->' + cast(inserted.isExtendedFlag as varchar(30))
END as 'isExtendedFlag'
;