• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 636
  • Last Modified:

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?

2 Solutions
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.
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.
j_heckAuthor Commented:
Both are great ways to complete the task.  Thanks.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now