MERGE is a DML command that enables us to optionally insert or update data into a target table, depending upon whether matching records already exist.
The MERGE statement is also known as UPSERT i.e. combination of UPDATE and INSERT. The MERGE statement is the best way to combine multiple operations, to avoid multiple INSERT and UPDATE.
One of the big advantages of the MERGE statement is that the entire data are read and processed only once.
Syntax:
SQL> MERGE
INTO dbo.TargetTable tgt -- Target Table
USING dbo.SourceTable src -- Source Table
ON tgt.ID = src.ID -- Main comparison criteria
WHEN MATCHED -- When ID's exist in both tables
THEN
-- DO SOMETHING
WHEN NOT MATCHED -- When ID's from Source do not exist in Target
THEN
-- DO SOMETHING
WHEN NOT MATCHED BY SOURCE -- When ID's from Target do not exist in Source
THEN
-- DO SOMETHING
Example :
Now we want to update the dept table from the dept_online table i.e. those rows which are already present in the dept table and the latest data from the dept_online table should be updated and those rows which are not present in dept table should be inserted.
SQL> SELECT * FROM dept;
DEPTNOu DNAME COUNTRY
10 ACCOUNTING USA
20 RESEARCH TEAM INDIA
30 SALES UK
60 HELP AMERICA
SQL> SELECT * FROM dept_online;
DEPTNO DNAME COUNTRY
40 OPERATIONS USA
20 RESEARCH INDIA
50 ENG ITALY
60 HELP DESK USA
SQL> MERGE
INTO dept d
USING dept_online o
ON (d.deptno = o.deptno)
WHEN MATCHED
THEN
UPDATE SET d.dname = o.dname, d.loc = o.loc
WHEN NOT MATCHED
THEN
INSERT (d.deptno, d.dname, d.loc) VALUES (o.deptno, o.dname, o.loc);
4 rows merged.
SQL> SELECT * FROM dept;
DEPTNO DNAME COUNTRY
10 ACCOUNTING USA
20 RESEARCH INDIA
30 SALES UK
40 OPERATIONS USA
50 ENG ITALY
60 HELP DESK USA