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