bteraberry (10/2/2012)
Depending on your timeline, you could do this with virtually zero downtime. Rather than doing a bulk copy, you could easily chunk this up since you're not looking for updates but only inserts (if I understand you correctly.) Just select top (1) id from the new table and then get the next N records from the old table after that ID to do the insert. Wrap each iteration in its own transaction, add a maxdop of whatever makes you comfortable (for something like this I would typically use 25% of my processors on a busy machine) and include a short WAITFOR DELAY after each iteration. With such a strategy you can easily plow through the copy without adversely affecting your server. You will still need to have a very short period of downtime to rename the old and then make sure you didn't miss any new records coming in before you rename the new, but it will be a matter of seconds instead of an hour.If you're not worried about the downtime so much, I believe the plan you have established will work fine.
bt,,
Can you elaborate more? Are you saying, inserting the remaining records (not copied via bcp) from old to new using SELECT INTO or something like that? Do you mind putting the T-SQL? Yes, that would be one of my steps, the final one before renaming the table and during the short offline, if I understand you correctly.
@Eugene,
Thanks for the suggestion. Quick question.
Not at work right now, but we do have several FK and Indexes on the source table. So I do need to drop and recreate Indexes on the new one after moving all the info.