<

MERGE command in Oracle

Published on
9,129 Points
3,129 Views
Last Modified:
Approved
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 ....])

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
);

Open in new window


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

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;

Open in new window


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

Sample data in 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 )
  );

Open in new window


Create Target Table
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;

Open in new window


Insert into Target Table
So the data on my target table will look like as below:

Sample data in Target Table
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; 

Open in new window


Function to return OS user
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');

Open in new window


Merge Command
Commit after executing the merge.

Now we can see the changes as below:

Final Output
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.
0
Comment
Author:Swadhin Ray
0 Comments

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Join & Write a Comment

Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month