Merge statement in SQL

MERGE is a DML command that enables us to optionally Update or Insert data into a target table, depending on whether matching records already exists or not. Lets see an example to understand more about the merge statement.

create table employee_source (EmpName varchar2(50),EmpID number)

create table employee_target (EmpName varchar2(50),EmpID number,Status varchar2(20))

Insert some dummy values in to both the table

In source table

insert into employee_source values('Rahi,Arpit',100);
insert into employee_source values('Sinha,Ankit',101);

EmpName EmpID
Rahi,Arpit 100
Sinha,Ankit 101


In target table

insert into employee_target values('Arpit',100,NULL);

EmpName EmpID STATUS
Arpit 100 NULL

Now the logic here is that we will try to insert record in to the employee target table.

We will change the status to inserted if the record is not already existing and we will change the status to updated if the record already exists.

Here you can see we have a matching record so we will now write a code to understand how exactly this will work.


merge
into EMPLOYEE_TARGET TGT
using EMPLOYEE_SOURCE SRC
ON ( src.empid = tgt.empid )
WHEN MATCHED
THEN
update
SET tgt.EMPNAME = src.EMPNAME
, tgt.status = 'Updated'
WHEN NOT MATCHED
then
insert ( TGT.EMPID
, tgt.EMPNAME
, TGT.STATUS )
values ( SRC.EMPID
, src.EMPNAME
, 'Inserted' );
/


Execute the following query and observe the result.

EMPNAME EMPID STATUS
Rahi,Arpit 100 Updated
Sinha,Ankit 101 Inserted
you will find that one record is inserted and one is updated.

This itself is self explanatory