SQL –Merge Statement

As name initiated, merge statement is used to synchronize the two tables so that the target table is modified with source table.
Merge statement basically combines the data from different table together on the basis of join fields in both table.
SQL Merge statement do insert and update/delete in target table basis on the row match/un-match in source table.

Usages:

• • It use to perform DML operation such as Insert/update/delete on a target table with join of both table in a single statement
  • It use to combine a different table as it itself called “UPSERT” i.e. insert row if not exists; update row if already exists in target table.

Advantages:

• No need to check data existence in target table for update and insert
• All records merge in one single statement basis of joining the different table
• It increases the performance of query.

Prerequisite:

• Both table require common field to join the different table
• Need to specify operation when condition match or unmatched to determine whether to do insert or update or delete

Syntax:
Merge statement have following syntax:

MERGE INTO [target_table] 
  USING source_table 
  ON   (target_table.id1=source_table.id1 and target_table.id2=source_table.id2.....)
  WHEN MATCHED THEN
  UPDATE SET column1 = value1 [, column2 = value2 ...]
  WHEN NOT MATCHED THEN
  INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...;

Clauses:
Clauses that appearing in above syntax has following description:

• Merge Into:specifying the target table for DML.
• Using:Specifying the source table to join with target table.
• ON:to match join fields in both tables.
• When:to specify the Insert/Update basis of match or unmatched

Example:

In this example, the source table is “tblSalary” and destination table is “DisburseSalary”. Let’s look on following figure, in this section I am going to apply merge statement so that all record merge in target table from source table.

MERGE INTO DisburseSalary AS T
USING tblsalary AS S
        ON T.empid = S.empid
WHEN MATCHED THEN
    UPDATE SET
      T.empname = S.empname,
      T.pay = S.pay
WHEN NOT MATCHED THEN 
      INSERT (empid, empname, pay)
      VALUES (S.empid, S.empname, S.pay);

Output: