MERGE command in Oracle

MERGE command is used to merge two tables like from a source to target table.
 In general the target table exists as acceptable data in the database where as the source table is really a table which containing the data which is not necessarily in the database yet, whereas some of the rows could be updated or inserted into the target table as new rows.

MERGE is used to insert new rows into the target table and update existing rows between the source and the target table.

MERGE statement syntax:

MERGE INTO [schema.]table [alias]
                      USING {[schema.]table|view|(subquery)} [alias]
                      ON (column = column [,column=column ...])
                      WHEN MATCHED THEN 
                        UPDATE SET {column ={column | expression| DEFAULT}}
                          [,column =...]
                      WHEN NOT MATCHED THEN 
                        INSERT (column [,column])
                          VALUES ({column |expression | DEFAULT}[,column ....])

Select all Open in new window

From the above syntax we can see that we use like “MERGE INTO” the target table “USING” the source table and with the join condition at “ON”.

For example I have created one sample table as SOURCE_TABLE as below:

SQL> create table SOURCE_TABLE
                      (
                      id number primary key,
                      COL1 varchar2(10),
                      COL2 number,
                      COL3 number
                      );

Select all Open in new window

Insert few records so that we can see how we can user merge on the target table. 

Select all Open in new window

SQL >
                      begin
                      insert into source_table values(1,'A',1,1);
                      insert into source_table values(2,'A',2,1);
                      insert into source_table values(3,'A',3,1);
                      insert into SOURCE_TABLE values(4,'B',1,1);
                      commit;
                      end;

Select all Open in new window

Here is the sample data on our source table i.e. “SOURCE_TABLE” :


Now let us create the target table as below:

SQL> CREATE TABLE target_table
                        (
                          id NUMBER PRIMARY KEY ,
                          col1 VARCHAR2( 10 ) ,
                          COL2 NUMBER ,
                          COL3 NUMBER ,
                          MODIFIED_BY VARCHAR2( 100 ) ,
                          CHANGE_DATE TIMESTAMP ,
                          change_type VARCHAR2( 100 )
                        );

Select all Open in new window

In  the target table I also used few additional columns which will help me to identify which user has updated or inserted  and when, along with that I also kept to have a track on what type done on the target table like was the row inserted or it’s been updated.
We can enhance this by implementing few more logic as per our business needs.

So now let us also insert few records into our target table which will tell us like whether it was an update or an insert by our merge command which we are going to use.

Run the below INSERT query on the target table :

SQL> BEGIN
                      INSERT INTO TARGET_TABLE VALUES(1,'A',1,1,'OTHER USER',SYSDATE-40,'INSERT');
                      insert into TARGET_TABLE values(2,'A',8,1,'OTHER USER',sysdate-40,'INSERT');
                      insert into TARGET_TABLE values(3,'A',3,1,'OTHER USER',sysdate-40,'INSERT');
                      commit;
                      END;

Select all Open in new window

So the data on my target table will look like as below:


From the above insert query you can see that I have used 'OTHER USER' on “MODIFIED_BY” column which specifies that it was been inserted by some other user (just for an example) and SYSDATE-40 on “CHANGE_DATE” column just have the track on the modification date. Finally the “CHANGE_TYPE” column, I made as ‘INSERT’ to mark as these are the rows which are inserted as new records but no updates are done.

Before going to MERGE command I will be using a simple function will return me the OS user name who has logged in:

SQL> select sys_context('USERENV','OS_USER') from dual; 

Select all Open in new window

So the below is my merge command for getting the records from my source table to target table:

SQL> MERGE INTO target_table tt
                      USING SOURCE_TABLE st
                      ON (st.id = tt.id)
                      WHEN MATCHED THEN 
                        update set 
                             tt.col1        =st.col1,
                             tt.COL2         = ST.COL2, 
                      			 TT.COL3         = ST.COL3,  
                      			 TT.MODIFIED_BY  =sys_context('USERENV','OS_USER'),
                      			 TT.CHANGE_DATE  = sysdate,
                      			 tt.CHANGE_TYPE  = 'UPDATE'
                      when not matched then 
                        insert (id,COL1,COL2,COL3,MODIFIED_BY,CHANGE_DATE,CHANGE_TYPE )
                          values (st.id,ST.COL1,ST.COL2,ST.COL3,sys_context('USERENV','OS_USER'),systimestamp,'INSERT');

Select all Open in new window

Commit after executing the merge.

Now we can see the changes as below:


Now we can have a track for like who had modified it at what time and we will also have a track like if it is inserted a new record or it’s an update.