Unfortunately, SQL Server's merge statement has a LOT of issues. One of the worst being that it is isn't atomic (without non-SQL-standard hints)[0]!
It really feels like it was a half-assed feature.
Great that they followed the standard, not so great that they made it nearly unusable with such a poor implementation.
I was super excited when we got SQL server 2008 at work (since upserts are a relatively common issue) and was devastated to learn all the issues with MERGE. I didn't care that the syntax was funky, I just wanted to avoid needing to manage transactions/locks/etc.
It really feels like it was a half-assed feature.
Great that they followed the standard, not so great that they made it nearly unusable with such a poor implementation.
I was super excited when we got SQL server 2008 at work (since upserts are a relatively common issue) and was devastated to learn all the issues with MERGE. I didn't care that the syntax was funky, I just wanted to avoid needing to manage transactions/locks/etc.
[0] https://weblogs.sqlteam.com/dang/2009/01/31/upsert-race-cond...