I need to create a stored procedure to move data from a table to an history table(same table def). After the data is moved I need to delete it from the first table. I have to ensure that the data is present in the archive prior to deletion of the data in the first table. All this will be happening 24x7 so impacting DB performance is a factor. Basically looking for the most efficient solution.

thanks!

Gunny

you can put everything in a transaction so if one of them fails, you can roll everything back and be in a safe position

Regards
Ganesh
http://ganeshprof.blogspot.com

I basically agree with Ganesh but would like to point out some things. If the sizes of the chunks of data you choose are too large your code will increase its likelyhood in failing in the event the data is changed while your moving it. If you select too small of a chunk of data the process will take a long time, you have to choose a balanced amount depending on your servers workload and the volume of data your dealing with.

well you may be right in one aspect..but the thing Gunny is going to do is a simple task which can be accomplished by two direct statements

Begin Transaction
INSERT <ColNames> INTO Tab2
SELECT <ColNames> FROM Tab1
WHERE <Condition>

DELETE Tab1 WHERE <Condition>
Commit Transaction

Regards
Ganesh
http://ganeshprof.blogspot.com