MERGE is a DML command that enables us to optionally update or insert data into a target table, depending on whether matching records already exist. In versions prior to 9i, we would have to code this scenario either in separate bulk SQL statements or in PL/SQL.
We can now see an example of the MERGE statement. In the following example, we will merge the source table into the target table. We will capture a count of the target table rows before and after the merge.
SQL> SELECT COUNT(*) FROM target_table;
1 row selected.
2 INTO target_table tgt
3 USING source_table src
4 ON ( src.object_id = tgt.object_id )
5 WHEN MATCHED
8 SET tgt.object_name = src.object_name
9 , tgt.object_type = src.object_type
10 WHEN NOT MATCHED
12 INSERT ( tgt.object_id
13 , tgt.object_name
14 , tgt.object_type )
15 VALUES ( src.object_id
16 , src.object_name
17 , src.object_type );
86889 rows merged.