What's the fastest way to INSERT loads of data from one table to another? (SQL Server 2005)

Question

I'm basically trying to copy data from a table in one database in SQL Server 2005 to another table, with the same structure (but lots of indexes) in another database in the same SQL Server instance.

My current approach is the obvious INSERT/SELECT:

set identity_insert TargetDBName.dbo.TableName on

insert into TargetDBName.dbo.TableName ([FieldsList])
  select [FieldsList] from  TargetDBName.dbo.TableName    

set identity_insert SourceDBName.dbo.TableName off

Which takes, approximately, forever (1 hour for 10 million records, while it took 20 minutes to do it from the table with indexes to the one without them).

What's the best way to do this?

Thanks!

Answer 1

I beleive your indexes will be recalculated on every insert, you should try disabling the indexes, perform bulk insert then enable them again ... see if that works

----Diable Index
ALTER INDEX [*INDEX_NAME*] ON *TABLE_NAME* DISABLE
GO
----Enable Index
ALTER INDEX [*INDEX_NAME*] ON *TABLE_NAME* REBUILD
GO

Answer 2

Check out this at StackOverflow

That should help you insert the data into chunks of 1000. I also like the 'disabling index idea'