The MERGE Syntax by Ajay Nerumati
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.
Comments