question

Bala avatar image
Bala asked

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

sql-server-2000insert
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yuriy Rozhok avatar image
Yuriy Rozhok answered

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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jay Bonk avatar image
Jay Bonk answered

Are they both within the same database? If so, you could use

INSERT INTO Table2 (.....)
SELECT .... FROM Table1
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.