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.