x

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

more ▼

asked Nov 11, 2009 at 01:29 PM in Default

avatar image

Bala
42 3 3 5

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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

more ▼

answered Nov 11, 2009 at 08:52 PM

avatar image

Yuriy Rozhok
66 2 1

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 11, 2009 at 02:10 PM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(comments are locked)
10|1200 characters needed characters left

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

INSERT INTO Table2 (.....)
SELECT .... FROM Table1
more ▼

answered Nov 11, 2009 at 01:33 PM

avatar image

Jay Bonk
1.4k 2 4

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x501
x136

asked: Nov 11, 2009 at 01:29 PM

Seen: 9125 times

Last Updated: Nov 11, 2009 at 02:06 PM

Copyright 2016 Redgate Software. Privacy Policy