Fastest way to insert data from table 1 to table 2
Hi
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.
Thanks
Best Answer
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:
- disable/drop constraints/triggers/indexes before you insert the records, and enable/recreate them after you inserted the data
- use TABLOCK hint (or use SERIALIZABLE isolation level), this will prevent lock escalation
- if possible, order your data in the manner declared by clustered index of target table (in this case you shouldn't drop clustered index)
- take care about target database size - increase its size before you insert the data; set its value using expected amount of data being imported
- create partitions on target table (for SQL2000 you might consider horizontally partitioned views), this way you'll be inserting data into single partition at the time
- if dataset is really large you might want to consider using DTS package or BCP utility
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.
