CODEWITHSUNDEEP
sqlsql-server-2008sqlbulkcopy
Aidan
Aidan

Reputation: 4891

Quickest way to bulk copy between tables

I have a SQL Server table with columns, call them A and B. I have created a new table with a type column and a single value column. I want to copy all the rows (there are 50 million) from the existing table to the new one, such that for each row in the first table there are 2 rows in the second (one for value A, one for value B). I have a simple script that will do this

insert into NewTable (ResultTypeId, Value)
(select @typeA, valueOfA from OldTable
union all
select @typeB, valueOfB from OldTable)

this naive approach takes about 8 minutes. Is there a quicker way to do this?

Upvotes: 0

Views: 1784

Answers (1)

Brandon R. Gates
Brandon R. Gates

Reputation: 318

Things to try:

  1. Load in two steps instead of using union. (100 million records is an awful lot to push in one transaction.)
  2. Don't create primary key or indexes on destination table until after the load.
  3. Use bulk insert instead of insert into ... select. Here's one place to start: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/b2b59376-e039-485b-84ef-613122a043f0/bcp-or-bulk-insert-why?forum=transactsql

Upvotes: 1

Related Questions