tab1 has about 1 milllion rows, tab2 has about 5 million rows. The idea is not locking these two tables one time for 5 minutes, instead, hopefully, multiple times, but seconds each time, thus, allow other programs to use these these tables in the mean time.
Index has been created on tab1.col1 and tab2.col1, but performance is still very slow.
"the reason it's hanging is because you're repeatedly dropping #Temp1 and reinserting the same values into it. Essentially, @@ROWCOUNT is never going to be equal to 0 so you'll never BREAK out of the loop."
How to work around this problem?
Thanks a lot