Merge statement in SQL Server

Merge is an ANSI standard DML statement and was introduced in SQL Server 2008 version. It was known as UPSERT (an informal term) in earlier versions of SQL Server. The special feature of this keyword is, to INSERT brand new records into the table and UPDATE the existing records upon a valid condition between source and target table.source-code

  • It compares the data between two identical tables (Source and Target)
  • AND condition can be specified to do INSERT/UPDATE/DELETE the only changes rather evaluating each and every row in the table.
  • OUTPUT clause works with MERGE.
  • MERGE requires a “;” at the end of the statement.
  • There are 3 types of clause can be used with MERGE as described below.

WHEN MATCHED: can perform UPDATE or DELETE rows into the TARGET table.
WHEN NOT MATCHED BY TARGET: can perform only INSERT new rows into the TARGET table.
WHEN NOT MATCHED BY SOURCE: can perform UPDATE or DELETE rows in the TARGET table.
WHEN NOT MATCHED ~ WHEN NOT MATCHED BY TARGET

MERGE-EXAMPLE-01

Example 01: (Used OUTPUT clause to watch MERGE statement in action)

  • WHEN MATCHED
  • WHEN NOT MATCHED BY TARGET

MERGE-EXAMPLE-02
  Two matching records are updated and three new records are inserted into Customer table from CustomerFeed. Now lets query on “Customer” table.

MERGE-EXAMPLE-03

Example 02: (Used OUTPUT clause to watch MERGE statement in action)

  • WHEN MATCHED
  • WHEN NOT MATCHED BY TARGET
  • WHEN NOT MATCHED BY SOURCE

MERGE-EXAMPLE-04Matching records are updated, New records are inserted from CustomerFeed and Not matching records between Coustomer and CustomerFeed are deleted from Customer table. Now Customer and CustomerFeed tables have identical records. Lets query on Customer table.
MERGE-EXAMPLE-05

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: