Merge Statement with Audits

Merge statement is used to insert, update or delete records in the destination table from the source table based on join condition, when trying to implement merger function its always handy to have some kind of auditing. Merge statement is pretty much the same when it comes to Oracle and SQL Server except implementing internal variables is different.

 

SQL Server

Script:

--create temporary table or table variable

DECLARE @count_table TABLE (count_column tinyint)

--write number of record insert, updates and deletes into the temporary table

INSERT @count_table (count_column)

SELECT

    m.counter

FROM

(

    MERGE Destination_Table AS d

    USING Source_Table AS s ON s.id = d.id

    WHEN NOT MATCHED THEN

        INSERT VALUES (s.id, s.val)

    WHEN MATCHED AND d.val <> s.val THEN

        UPDATE SET d.val = s.val

    WHEN MATCHED THEN DELETE

    OUTPUT

        CASE

            WHEN $action = N'UPDATE' THEN CONVERT(TINYINT, 1)

            WHEN $action = N'DELETE' THEN CONVERT(TINYINT, 3)

            WHEN $action = N'INSERT' THEN CONVERT(TINYINT, 4)

        END

        AS counter

) AS m;

--here insert the record counts into the actual audit table

insert into <<Audit Table>>SELECT

        CASE c.count_column

            WHEN 1 THEN 'Update'

            WHEN 3 THEN 'Delete'

            WHEN 4 THEN 'Insert'

        END as Action_type,

     COUNT(*) as record_count

FROM @Count_Table AS c

GROUP BY

    c.count_column

 

Oracle:

 

Oracle allows Parallel DML merge, all we have to add is "ALTER SESSION ENABLE PARALLEL DML;" statement at the top in the below script.

 

Script:

--create temporary table or table variable

CREATE GLOBAL TEMPORARY TABLE count_table ( count_column NUMBER( 10, 0 ) ) ON COMMIT DELETE ROWS;

Begin

--write number of record insert,updates and deletes into the temporary table

INSERT count_table (count_column)

SELECT

    m.counter

FROM

(

    MERGE Destination_Table AS d

    USING Source_Table AS s ON s.id = d.id

    WHEN NOT MATCHED THEN

        INSERT VALUES (s.id, s.val)

    WHEN MATCHED AND d.val <> s.val THEN

        UPDATE SET d.val = s.val

    WHEN MATCHED THEN DELETE

    OUTPUT

        CASE

            WHEN $action = N'UPDATE' THEN  1

            WHEN $action = N'DELETE' THEN  3

            WHEN $action = N'INSERT' THEN  4

        END

        AS counter

) AS m;

--here insert the record counts into the actual audit table

insert into <<Audit Table>>

SELECT

        CASE c.count_column

            WHEN 1 THEN 'Update'

            WHEN 3 THEN 'Delete'

            WHEN 4 THEN 'Insert'

        END as Action_type,

     COUNT(*) as record_count

FROM @Count_Table AS c

GROUP BY

    c.count_column;

 end;

 

Thank you for reading this article, check out my other SQL Server vs. Oracle posts.