There is a big table (5-6 million records). I have to move 90% of old records to other database(table). Solution?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
Couple of additions to Rolando's suggestion.
If you're clearing out the old table, as well as populating new you could use the OUTPUT clause. Be mindful of the potential for log growth, consider a loop/batch approach if this may be a problem.
BCP is a handy alternative to be aware of. Note this is using SQLCMD syntax.
For SQL-Server the real problem is avoiding the logging of the deleted rows.
My proposal is Set up a second database in simple recovery mode. Copy the the hole table with
If target Server Is SQL-Server of same revision you can move the data by backup and restore.
In other cases take some bulkcopy option.
Although it is time to learn about partioned tables, but that seems to be an option only for Enterprise Edition.
If the TableA is really big, you can use bulk-operations and bcp utility
see the About Bulk Import and Bulk Export Operations article.