HunterJ (2/18/2013)
Hi Gail,In what isolation level is the query that checks the results running? Do you have Read Committed Snapshot on?
Also, have you considered using the DELETE...OUTPUT clause to capture the results that should be inserted into the new table, saving you first having to SELECT into your table variable?
http://msdn.microsoft.com/en-gb/library/ms189835.aspx#CaptureResults
Jamie
Thanks for responding, Jamie.
The query that checks the results is operating at the READCOMMITTED isolation level. And, in fact, the data migration query I wrote was originally operating at the same level which should be completely adequate. I escalated the isolation level to SERIALIZABLE just to see if it would fix the problem.
I have considered the DELETE...OUTPUT clause but I think what I've already written should work. I want to understand what the problem is before moving on to another way of implementing the code.