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!

score 5 · Answer 1 · edited Mar 31 '11 at 12:38

5

I believe 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

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

edited Mar 31 '11 at 12:38

Filip De Vos

11,568
1
48
60

answered Jun 05 '09 at 19:48

Adam Fox

1,316
2
12
24

Might want to format that a bit better, right now everything above the second "ALTER INDEX" is going to be commented out. – Brian Sullivan Jun 05 '09 at 19:50
I agree... disable or drop/add the indexes to your destination during the load. – Zachary Jun 05 '09 at 19:53

score 1 · Answer 2 · edited May 23 '17 at 11:55

1

Check out this at StackOverflow

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

edited May 23 '17 at 11:55

Community

1
1

answered Jun 05 '09 at 19:50

Eric

7,930
17
96
128

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

2 Answers2