SQL MERGE Statement - SQL Server, Oracle

The MERGE statement selects the rows from one or more tables (called Source table), and based on conditions specified, INSERT or UPDATE data to another table (called Target table).

Syntax:

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);

To understand the MERGE statement, assume that you have two tables, Consultant and Employee. Now, you want to copy the data from the Consultant to the Employee table based on the condition that if an employee already exists in the Employee table, then update its FirstName and LastName columns. If a consultant does not exist in the Employee table, add a new record in the Employee table.

MERGE INTO Employee TARGET
USING Consultant SOURCE
ON TARGET.EmpId = SOURCE.EmpId
WHEN MATCHED THEN 
    UPDATE TARGET.FirstName = SOURCE.FirstName, TARGET.LastName = SOURCE.LastName
WHEN NOT MATCHED THEN
  INSERT into Employee(EmpId, FirstName, LastName)
  VALUES(SOURCE.EmpId, SOURCE.FirstName, SOURCE.LastName);