All Forums Database
13 Dec 2012
Merge Over Update

What are the advantages of using Merge ? and when do we prefer using MERGE over the UPDATE
Does using a merge give  better performance every time ?

Wasif.Kabeer 12 posts Joined 11/11
13 Dec 2012

Traditionally, the MERGE is considered to be a better choice than Update & Insert (UPSERT) due to it's strength and flexibility.
The Update and Insert part need to reference: 

  • The same table
  • The same row
  • Should use the PI for a respective operation
  • PI shouldn't be an Identity Column

The Insert portion may specify different partitions
The Update can modify partitioning fields
Partitioning fields need not be specified in WHERE of Update portion
For additional details, please refer:

13 Dec 2012

Thanks Wasif

ToddAWalter 316 posts Joined 10/11
14 Dec 2012

MERGE and UPDATE perform two different tasks. UPDATE only updates existing rows. MERGE tests to see if the row exists, performs an INSERT if not and performs and UPDATE if the row exists. If only UPDATEs are to be performed, the UPDATE should be used. If the additional functionality is required, then MERgE is a much better and much better performaing answer than two passes, one to do the UPDATEs and one to do the INSERTS.

You must sign in to leave a comment.