Insert affected rows to temp table before inserting using Merge Statement SQL Server 2008 R2

653 Views Asked by At

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'

        ;
0

There are 0 best solutions below