Role of MERGE command? What's Upsert Functionality?

Question: Role of MERGE SQL command? What's Upsert Functionality?

Answer: Oracle 9i introduced a new SQL command named 'MERGE' and this command is used for implementing conditional INSERT and UPDATE commands in s single atomic statement. This functionality is commonly knwon as 'Upsert Functionality'.

This MERGE command comes very handy especially in the situations where on-line data requires the database to be refreshed periodically. The newly arrived data may require the existing rows of table(s) to be changed and/or new records to be inserted into the table(s) based on certain conditions. This is what MERGE was invented for.

Advantages of using MERGE

Prior to Oracle 9i, all such activities used to be performed using a series of DML statements or by using a PL/SQL program and both the approaches had performance issues for obvious reasons. The former required many data scans whereas the latter required the changes to be made in the database on a per-record basis. Usage of MERGE not only improves the performance in such scenarios, but also makes the overall functionality pretty simple.

Example: SQL statement using MERGE command to INSERT/UPDATE in a single atomic statement:-

MERGE INTO TABLE_NAME T1
USING ANOTHER_TABLE_NAME T2
ON (T1.KEY1 = T2.KEY1 AND T1.KEY2 = T2.KEY2 AND ...)
WHEN MATCHED THEN
UPDATESET COLUMN_NAME1 = NEW_VALUE_FOR_THE_COLUMN, ...
WHEN NOT MATCHED THEN
INSERT (T1.COL1, T1.COL2, ....) INTO VALUES (T2.COL1, T2.COL2, ..);