SQL –Merge Statement

0
2124
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.
  • image1

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:

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.

image2

Output:
image3

LEAVE A REPLY

Please enter your comment!
Please enter your name here