I have the following table:
And the following stored procedure:
This works as I expect it to, and I can uncomment the waitfor delay line and run it in different query windows and it works as expected when used from multiple places at the same time.
Now my question is this: Is there is more efficient/elegant way to do this (In SQL 2000)
asked Feb 23, 2010 at 09:28 AM in Default
My Other Me
answered Feb 23, 2010 at 10:40 AM
My typical method for upsert in the past has been like this, though I've built it to work with multiple rows, not just a parameter.
In this case I might
You'd have to wrap in a transaction, and concurrency could be an issue. If you dropped the changes into a table and wrapped a series of updates/insert together, it ought to handle multiple threads better. Is there a PK on word?
answered Feb 23, 2010 at 01:00 PM
Steve Jones - Editor ♦♦
No, but - do bear in mind that the only reason that yours works is because the query optimiser doesn't actually really give a crap about your
Think about it, how can you hold a row-level lock on a row that doesn't yet exist?
So - tread carefully, because the concurrency on this will suck. An alternative way to do it would be as the other people have posted, but using a
answered Feb 23, 2010 at 06:26 PM
Matt Whitfield ♦♦
Here's a method I have used before.
answered Feb 23, 2010 at 09:41 AM
Kev Riley ♦♦