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?
Things to try:
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