GilaMonster (7/25/2016)

---

Lynn Pettis (7/25/2016)

---

GilaMonster (7/25/2016)

---

fergfamster (7/25/2016)

---

You dont think using the @@rowcount is useful? Better to just do both statements?

No, because what if there's 6 rows that need inserting and 4 updating? To use rowcount, you have to loop over those 10 rows. With Lynn's way you just run both statements, the update affects the 4 existing rows and the insert affects the 6 rows that weren't in the table at the start.

The exists needs lock hints though (as in my article), otherwise there's still a chance of duplicates if there are multiple sessions running this concurrently.

Even with this? Just want to be sure.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Sorry, not duplicates. Deadlocks.

Multiple sessions take a shared range lock, multiple sessions go to convert the lock to X for the insert, can't be converted due to the shared range locks, instant deadlock.

I'd recommend use the locking hints and not the elevated isolation level, and it only needs the transaction if it's there for rollbacks on errors (and then with a try catch block).