Quickest way to bulk copy between tables

sql sqlbulkcopy sql-server-2008

Question

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?

Popular Answer

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


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why