Oracle Merge Statement Delta Detection

The MERGE Syntax by Ajay Nerumati

Delta Detection in Oracle SQL

Posted on 8. October 2016

Delta detection is a common task in every Data Warehouse. It compares new data from a source system with the last versions in the Data Warehouse to find out whether a new version has to be created. There are several ways to implement this in Oracle.

Your source system delivers a full extraction every night, and you have to load only the changed rows into your Core Data Warehouse? You receive incremental loads from another source system every few minutes, but only a few columns are loaded into the Data Warehouse.

In all these situations, you need a delta detection mechanism to identify the rows that have to be inserted or updated in your Data Warehouse tables. In this blog post, I want to show different methods in Oracle SQL that provide the subset of rows of a source table that were changed since the last load. All these methods are set-based, i.e. they can be executed in one SQL statement.

Merge is used to conditionally insert or update a table .

To understand the merge statement we will use two tables DEPT and DEPT_COPY. The objective being that we load data from DEPT table conditionally into DEPT_COPY .

Step 1  Preparing DEPT and DETP_COPY tables

Based on a much familiar table DEPT that has 4 records, a new table DEPT_COPY with no records  is created using the following syntax .

create table dept_copy as select * from dept where 1=2 ;

Check if the table "DEPT_COPY" is empty and without any records using the following syntax.

Select * from DEPT_COPY;

Step 2  Understanding the Merge Syntax before implementation

INTO  clause specifies the target table you are updating or inserting into.

USING clause identifies the source of the data to be updated or inserted; can be a table, view, or subquery

ON clause the condition upon which the MERGE operation either updates or inserts

WHEN MATCHED | WHEN NOT MATCHED instructs the server how to respond to the results of the join condition

  

Step 3 Implementing the Merge Syntax

Execute the following syntax:

MERGE INTO dept_copy c

USING dept d

ON (c.deptno = d.deptno)

WHEN MATCHED THEN

UPDATE SET c.dname = d.dname, c.loc = d.loc

WHEN NOT MATCHED THEN

INSERT VALUES(d.deptno, d.dname, d.loc);

After executing the above command all the data from table "DEPT" is loaded into "DEPT_COPY".

Query the table DEPT_COPY to confirm that it is populated with all the 4 rows from DEPT.

Step 4 Checking for UPDATES on DEPT to reflect in DEPT_COPY

To test the effect of  MERGE command , make modifications to the source table "DEPT" as follows :-

update dept set loc ='HYDERABAD' where deptno =10;

commit;

Re-run the same MERGE command without making any changes.

MERGE INTO dept_copy c

USING dept d

ON (c.deptno = d.deptno)

WHEN MATCHED THEN

UPDATE SET c.dname = d.dname, c.loc = d.loc

WHEN NOT MATCHED THEN

INSERT VALUES(d.deptno,d.dname,d.loc);

Query the target table "DEPT_COPY" to check if the changes for the loc column in the source "DEPT" are reflecting in the target table.

Select * from DEPT_COPY.

you should find all the updates done on source reflecting in the target.

Step 5 Checking for INSERT on DEPT to reflect in DEPT_COPY

Insert the following row into DEPT table and run the merge command.

insert into dept values ( 50, 'Software', 'Mumbai');

commit;

check the insert table in the source by querying

select * from dept;

Re-run the MERGE command without making any changes to check if the record is loaded into DEPT_COPY.

MERGE INTO dept_copy c

USING dept d

ON (c.deptno = d.deptno)

WHEN MATCHED THEN

UPDATE SET c.dname = d.dname, c.loc = d.loc

WHEN NOT MATCHED THEN

INSERT VALUES(d.deptno,d.dname,d.loc);

Query the target table "DEPT_COPY" to check if the changes in the source "DEPT" are reflecting in the target table.

Select * from DEPT_COPY.

You should find any inserts done on source reflecting in the target.

Merge statement,  can update or insert a row conditionally into a table, thus avoiding multiple UPDATE statements. The decision whether to update or insert into the target table is based on a condition in the ON clause.