What Are The Advantages Of Merge Over Simple If Exists?

I want to know what are the advantages of MERGE over simply using IF EXISTS. Which is the suggested approach? Does MERGE performs Update and Insert row-by-row matching conditions?

Solution 1:

MERGE combines INSERT, UPDATE and DELETE logic into one DML statement, and therefore is atomic. If you are doing single row UPSERTS then the advantages are less obvious. For example, a naive implementation of an UPSERT may look like the following:

IF  EXISTS (SELECT*FROM t1 where id=@id)
    UPDATE t1 SET ... WHERE id=@idELSEINSERTINTO t1 (...) VALUES (...)

However, without wrapping this in a transaction, it is possible that the row we're going to update will be deleted between the SELECT and the UPDATE. Adding minimal logic to address that issue give us this:

BEGIN TRAN
IF  EXISTS (SELECT*FROM t1 WITH (HOLDLOCK, UPDLOCK) where id=@id )
    UPDATE t1 SET ... WHERE id=@idELSEINSERTINTO t1 (...) VALUES (...)
COMMIT

This logic isn't necessary with the MERGE statement.

There are no comparisons that should be drawn between CURSORS and the MERGE statement.

Solution 2:

Merge will give you the option of updating, inserting and deleting data in a target table where is it matched in a source table. It is a set based operation so is not like a cursor (row by row)

I am not sure how you mean by advantages over 'IF EXISTS', but merge is a useful and flexible way of synchronizing 2 tables

this is a useful resource for merge https://www.simple-talk.com/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/