Copying from 1 table to 3 and keeping in sync during long operations 1

[adam0101]

On a routine basis, I need to copy upwards of 10,000 records matching certain criteria from one table to three others. My concern is that users could insert rows matching the criteria during one of the operations - causing the subsequent operations to copy a different number of records.

Right now I do a SELECT INTO to put the primary keys of the records to copy into a temp table and then perform the copy operation for each destination table by joining the temp table to the source table, but like I said, there can be upwards of 10,000 records. I don't know if creating a temp table that big is a good idea - even if it only holds primary keys.

What other options are there? Thanks!

Adam

http://adameslinger.blogspot.com/

 

[mrdenny]

Creating a temp table that size is no problem.

You could also use a table variable and do the same thing.

You also have the option of using the TABLOCKX query hint (or SET ISOLATION LEVEL SERIALIZABLE) which will put an exclusive lock on the table and prevent anyone else from using the table until your transaction is complete.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[adam0101]

Thanks for responding. Locking the records is not an option for me. I've discovered I need to create the temp table for another part of the process anyway, so I'll just stick with that. If I didn't have to use a temp table, I noticed that the source table has a "Last Modified" date column - so another option might have been to set a variable to the current time at the beginning of the procedure and then make sure any records exported where only modified before that date. Thanks again!

Adam

http://adameslinger.blogspot.com/