将数据加载从一个表插入另一个表的最快方法是什么？ （SQL Server 2005）

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.

我基本上试图将数据从SQL Server 2005中的一个数据库中的表复制到另一个表,在同一个SQL Server实例中的另一个数据库中具有相同的结构(但很多索引)。

My current approach is the obvious INSERT/SELECT:

我目前的方法是显而易见的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).

这大概是永久的(1000万条记录需要1小时,而从索引表到没有索引的表需要20分钟)。

What's the best way to do this?

最好的方法是什么?

Thanks!

2 个解决方案

#1

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

#2

Check out this at *

在*上查看这个

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

这应该可以帮助您将数据插入到1000块中。我也喜欢“禁用索引的想法”