Best way to Select, copy and delete large amount of rows

Last Post 27 Nov 2007 01:18 PM by nosepicker. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
phsatech
New Member
New Member

--
27 Nov 2007 08:32 AM
I have a table that is quite large (65million rows) and looking to archive 1/2 of it to another database with the same table definitions. I want to Select 1/2 of that from the source table and Insert it to another database that has the exact same table definition as the source and then delete the copied data from source. What is the best way to achieve this without creating a large tempdb?

Appreciate replies.
nosepicker
New Member
New Member

--
27 Nov 2007 01:18 PM
Doing this kind of operation shouldn't touch the tempdb, unless your select statement(s) use a join that requires a hash table. What you would have to be mindful of is the growth of your transaction log file. If you can put your other database (the one that will receive the archived data) into bulk-logged recovery mode, you can then use some sort of bulk load operation (SSIS for sql2005, DTS for sql2000, or bcp) to move the data. When I do this, I usually do it in smaller parts so the log file doesn't grow too large (bulk-logged mode will keep the growth small during a bulk load operation, but it will grow nonetheless). If the target database is in bulk-logged mode, you can truncate the log file in between parts to keep the size smaller. If the target database is in full recovery mode, you can backup the log in between parts. Then, when it comes to the source database, you can either delete the data in parts as well. If your source table has a lot of indexes, it might be faster to select the data that you want to keep into a new table (in smaller parts again) and then recreate the indexes afterward. Don't forget that creating indexes grows the transaction log as well.
AussiePete
New Member
New Member

--
03 Dec 2007 07:54 PM
Deleting half the data in a table can take a long time. It might be faster to throw away all the data and bulk load the half you want to keep. It sounds odd, but it has worked for me in the past. You dont want to DELETE data as that is a fully logged operation.

EG1 BCP out to file the data to keep, truncate the table, load the data back in. (assumes no FK on table)
EG2 rename the table, create a new table (explicitly or use select into), load in the data to retain using bcp/ssis/select into.

Both examples assume that you have exclusive access to the table for the duration.

As mentioned above, dont forget to set your recovery mode to bulk logged. It is better to drop indexes, load the data, and create the indexes at the end.
You are not authorized to post a reply.

Acceptable Use Policy