
Please don't. Every time I see a question about MERGE
, I scramble for the links I post to urge people to change their minds. So I put them together here.
If you insist on using MERGE
, the biggest TL;DR is: Please ALWAYS use HOLDLOCK
on the target.
- Use Caution with SQL Server's MERGE Statement
This is the one that occasionally ruffles feathers, but I talk about some real shortcomings withMERGE
and some bugs that have never been fixed. - Be Careful with the Merge Statement
Michael J. Swart initially advised you be careful withMERGE
. - What To Avoid If You Want To Use MERGE
In a follow-up post, Michael pulls no punches here, except the punchline: his now stronger opinion that you just avoidMERGE
altogether. - Please stop using this UPSERT anti-pattern
When I do succeed in weening someone away fromMERGE
, they often turn to a problematicUPSERT
approach. - SQL Server UPSERT Patterns and Antipatterns and Mythbusting: Concurrent Update/Insert Solutions
Michael also talks about how to perform concurrent updates/inserts. - UPDATE if exists else INSERT or Solutions for INSERT OR UPDATE on SQL Server
Probably the most canonical answers I have that explain why you should read all the links above. - Some MERGE bugs from Paul White
Paul goes into great detail about several bugs he's discovered withMERGE
over the years.