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

Bala gravatar image

Bala
42 3 3 3

(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

Yuriy Rozhok gravatar image

Yuriy Rozhok
66

(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

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

Disable any constraints will also increase the performance.
Nov 11, 2009 at 02:58 PM Håkan Winther
Yep - good shout, i'll edit my answer...
Nov 11, 2009 at 03:12 PM Matt Whitfield ♦♦
Depends whether the insert needs to be transactionally consistent. If so, then Yuriy's answer looks better. If there's a failure partway through you can't rollback - admittedly you can just truncate the table, but still it doesn't satisfy the ACID properties of a transaction.
Nov 12, 2009 at 06:08 AM Mark Allison
Which part of that do you think can't go in a transaction? The only bits of DDL which can't be transacted are listed at http://msdn.microsoft.com/en-us/library/ms191544.aspx...
Nov 12, 2009 at 06:24 AM Matt Whitfield ♦♦
(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

Jay Bonk gravatar image

Jay Bonk
1.4k 2

This should work in most cases, but it is not the fastest and it probably will fail if the tables are a couple of hundred millions of rows and a row size of 1kb. It will take too long and will consume a lot of transaction log space. I have filled the disks with the log files more than once. :)
Nov 12, 2009 at 04:28 AM Håkan Winther
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x473
x106

asked: Nov 11, 2009 at 01:29 PM

Seen: 8024 times

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