How do I make SQL Server commit inserts in chunks? I need to copy a large amount of rows from an old db into a new table, and it has several problems:
it takes ages to finish, and I don't see any rows in my table until the entire transaction is finished.
my log file is growing like crazy and it will probably run out of space.
if something breaks in the middle, I have to repeat everything.
If I add SET ROWCOUNT 500, I can limit the number of rows, but I don't know how to continue with the last inserted ID. I might query the new table to see what got inserted last, but I am not sure if that's the right thing to do. And it's a bit difficult because my where clause does not use the ID column, so I am not sure how to know exactly where to continue.
What's the best approach for this? Is there a "for loop" or something which would allow me to commit every once in a while?
I am using SSMS for SQL Server 2008 R2.
Even if TomTom's answer is sarcastic, it contains two basic options, which may help you:
You can write a loop in T-SQL (see for example while) and use TOP and OFFSET to select chunks (you need an order by). You can minimize looging according to Microsoft. And if you just worry about restarting without redoing everything this should be fine, though I don't expect it to be fast.
You can export your selection to a file and use the bulk insert to load it.
Some more options you may find here (About Bulk Import and Bulk Export Operations) and here (INSERT Section Best Practices).