What's the best way to copy a millions rows from one sql server database to another?

Question

I have one database that gets a million rows added to it on a daily basis. Every day I'd like to run a query to pull out the million oldest rows(leaving about ten million in the database), and then move them into an archive database.

There are well documented ways of doing this with sql server(bcp, openrowset, etc), but from what I can tell, all of those methods take all of the rows from a data file. I only want to copy a subset of the rows, and I can't find any quick ways of doing that.

Answer 1

Have you explored using SSIS to do this? Specifically their bulk operations components:

http://msdn.microsoft.com/en-us/library/ms141239.aspx

Might be worth taking a look at.

Answer 2

I would suggest considering an SSIS package. You can control exactly what rows get copied, you can do it in transaction sizes of your choice, and it will do a fast bulk insert.