0

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?

Aidan
  • 4,783
  • 5
  • 34
  • 58
  • 1
    Could you use SSIS for this? This will split your insert into more manageable chunks, so you don't have a single transaction for your entire insert. You can also manage whether constraints are checked or not during the insert, disabling this (as long as you know your data does not violate them) will also speed up the insert. – GarethD Feb 14 '14 at 11:03
  • Yes, and SSIS will let you do parallel loads. Handy if the server has gobs of CPU and RAM. Personally, I think SSIS comes with a big learning curve and adds additional maintenance/portability hassle, so I rarely use it. SP or death is my general rule ... – Brandon R. Gates Feb 15 '14 at 01:07

1 Answers1

1

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
Brandon R. Gates
  • 318
  • 1
  • 7