Whats the best (and fastest) way to insert a large amount of data from table 1 to table 2. Both having identical table structures. I am using SQL Server 2000.
If table 2 is empty, it should be faster to use another statement:
SELECT * INTO Table2 FROM Table1
Notice, this will create the table for you, so it should not exist at the moment of the operation. It works faster because SELECT INTO is minimally logged operation. You should also switch the database to Bulk Logged recovery model for the time of copying. Then you'll be able to switch it back to the original model. Other options to decrease the time are:
Are they both within the same database? If so, you could use
INSERT INTO Table2 (.....) SELECT .... FROM Table1
The fastest way is to drop the indexes and constraints on table 2, insert the data in small chunks (say 10,000 rows at a time), then re-create the indexes on table 2. You'll only see the benefits of this method in the million+ row instances, and it's not something I'd advise to do on anything but a one-time basis.
No one has followed this question yet.