CODEWITHSUNDEEP
sql-servert-sql
MgSam
MgSam

Reputation: 12803

Merge: order of effects

I'm trying to use a MERGE command to insert and update some data in a table. The table has a unique key on it, such that the update command must run before the insert, or else it will fail due to a violation of the unique key. This merge operation also takes place in the context of a transaction that runs other queries as well.

Here is a general outline of the query:

MERGE [dbo].[MyTable] AS Target
USING #temp AS Source ON Target.[id] = Source.[id]
WHEN MATCHED THEN 
  UPDATE ...
WHEN NOT MATCHED THEN 
  INSERT ...

My problem is that when I run the query it is throwing an error for the unique key violation. I'd hope that the update clause gets run first, but it doesn't look like that's happening.

Can anyone confirm the order of execution and if there's anything I can do to fix this?

Upvotes: 0

Views: 699

Answers (1)

M.Ali
M.Ali

Reputation: 69514

Avoid using Merge statement, I would use two separate UPDATE and INSERT statement to do these operations, something like this....

UPDATE

UPDATE MT
 SET MT.Col1 = T.Col1
    ,MT.Col2 = T.Col2
    ,MT.Col3 = T.Col3
FROM [MyTable] MT INNER JOIN #temp t
ON MT.[id] = T.[id]

INSERT

INSERT INTO [MyTable] (ID, Col1, Col2, Col3)
SELECT t.ID, t.Col1, t.Col2, t.Col3
FROM #temp t
WHERE NOT EXISTS (SELECT 1
                  FROM [MyTable] 
                  WHERE ID = t.ID)

Why Avoid MERGE

Read this Article by Aaron Bertrand Use Caution with SQL Server's MERGE Statement

After reading this article you will learn this violation of Unique Key is infact an Active bug in Merge Statement. Better stay away from Merge :)

Upvotes: 2

Related Questions