MS SQL 2008 Bulk Copy Records between two tables in same database

I have a Current table and an Archive table.  I want to move records from the Current (has an identity field) table to the Archive table for all records that are greater than 30 days old.  I know I can do this with SQL scripting which will probably work ok for all archiving after the initial archiving routine.  To start I have 300+ million records in the Current table and will be moving all but about 25 million of those records to the Archive table.  Is there any fast way to do this?

Thanks,
John
j_heckAsked:
Who is Participating?
 
ZberteocCommented:
1. Rename your existing table to table_archive.
2. Create back the table with the same structure as the table_archive.
3. Copy the 25M rows from table_archive to table
4. Make sure you have a timestamp column with a default of GETDATE() that is indexed and then you can remove easily the ones that are more that 30days old.
0
 
Scott PletcherSenior DBACommented:
Copy all rows to the archive table and then delete the 25M you don't need.

For the current table, pull out the 25M you do need, truncate the table, then put the good 25M back.
0
 
j_heckAuthor Commented:
Both are great ways to complete the task.  Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.