SQL Server 2005: stored procedure to move rows from one table to another

Question

Hello,

I have 2 tables of identical schemea. I need to move rows older than 90 days (based on a dataTime column present in the table) from table A to table B. Here is the pseudo code for what I want to do

SET @Criteria = getdate()-90

Select * from table A
Where column X<@Criteria
Into table B

--now clean up the records we just moved to table B, in Table A
delete from table A Where column X<@Criteria

My questions are:

1. What is the most efficient way to do this (will select-in perform well under high volumes)? Table A will have ~180,000,000 rows in it, and will need to move ~4,000,000 rows at a time to table B.

2. How do I encapsulate this under one transaction so that I will not delete rows from Table A if there was an error inserting them to Table B. I just want to make sure that I don't accidentally delete a row from table A unless I have successfully written it to table B.

3. Are there any good SQL Server 2005 books that you recommend?

Thanks, Chris

Answer 1

I think that SSIS is probably the best solution for your needs.

I think you can just use the SSIS tasks like Data Flow task to achieve your needs. There doesnt seem to be any need to create a procedure separately for the logic.

Transactions can be set for any Data Flow task using TransactionOption property. Check out this article as to how to use Transactions in SSIS

Some basic tutorials on SSIS packages and how to create them can be referred to here and here

Answer 2

regarding How do I encapsulate this under one transaction so that I will not delete rows from Table A if there was an error inserting them to Table B.

you can delete all rows from A that are in B using a join. Then, if the copy to B failed, nothing will be deleted from A.