The MERGE command in SQL is actually a combination of three SQL statements: INSERT, UPDATE and DELETE. In simple words, the MERGE statement in SQL provides a convenient way to perform all these three operations together which can be very helpful when it comes to handle the large running databases. But unlike INSERT, UPDATE and DELETE statements MERGE statement requires a source table to perform these operations on the required table which is called as target table.
Now we know that the MERGE in SQL requires two tables : one the target table on which we want to perform INSERT, UPDATE and DELETE operations, and the other one is source table which contains the new modified and correct data for target table and is actually compared with the actual target table in order to modify it.
MERGE statement in SQL basically merges data from a source result set to a target table based on a condition that is specified. The syntax of MERGE statement can be complex to understand at first but its very easy once you know what it means.So,not to get confused first let’s discuss some basics. Suppose you have two tables: source and target, now think if you want to make changes in the required target table with the help of provided source table which consists of latest details.
Example -
MERGE INTO target_table_name or target_table_query
USING source_table_name or source_table_query
ON (list_of_conditions)
WHEN MATCHED THEN
UPDATE target_table_name SET target_table_name.column_1 = source_table_name.expr_1, target_table_name.column_2 = source_table_name.expr_2,...target_table_name.column_n = source_table_name.expr_n
WHEN NOT MATCHED THEN
INSERT (column_1,column_2...column_n)
VALUES(source_table_name.expr_1, source_table_name.expr_2,...source_table_name.expr_n);
