logging - 在使用合并语句 SQL Server 2008 R2 插入之前将受影响的行插入临时表

Question

我创建了以下MERGE语句，它按预期工作。

不幸的是，由于新的要求，我需要修改这个合并语句。在通过语句插入/更新/删除之前MERGE，我需要将行捕获到第三个表中。用户审核后，我可以将更改推送到 TARGET 表。

MERGE声明可以做到这一点吗？

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'

        ;

标签： loggingsql-server-2008-r2merge-statement