BulkCopy从存储过程 [英] BulkCopy from Stored Procedure

问题描述

我有表A，B和C的数据库。我必须把从A和B获得到表C中的结果。

I have tables A, B and C in database. I have to put the result obtained from A and B into table C.

目前，我有一个返回A的结果的SP和B到C＃应用程序。这一结果将通过System.Data.SqlClient.SqlBulkCopy被​​复制到表C中。该advanatge是使用插入bulkcopy期间​​，登录不创建的文件。

Currently, I have an SP that returns the result of the A and B to the C# application. This result will be copied into table C using "System.Data.SqlClient.SqlBulkCopy". The advanatge is during the insert using bulkcopy, log files are not created.

我想避免这种额外的流量，通过处理SP本身的插入。然而，它不应该使用任何日志文件。任何的方式来实现这一目标？

I want to avoid this extra traffic, by handling the insert in the SP itself. However, it should not be using any log files. Any way to achieve this?

请分享你的看法。

体积的数据 150,000

数据库：SQL Server 2005的

Database : SQL Server 2005

数据库处于完全恢复模式;它不能被改变。是的 SELECT INTO 有用在这样的场景？

The database is in full recovery model; it cannot be changed.. Is SELECT INTO usefull in such scenario?

编辑：当我使用System.Data.SqlClient.SqlBulkCopy，该操作在3 mnutes得到完成;在正常插入需要30分钟......这个特殊的操作不需要被收回;在数据库然而其他操作被recoveed - 因此我不能改变整个数据库的恢复模式

When I use System.Data.SqlClient.SqlBulkCopy, the operation is getting completed in 3 mnutes; in normal insert it takes 30 minutes... This particular operation need not be recovered; however other operations in the database has to be recoveed - hence I cannot change the recovery mode of the whole database.

感谢

Lijo

推荐答案

您可以使用 SELECT INTO 与 BULK_LOGGED 作为 INTO子句文档（MSDN）：

ALTER DATABASE AdventureWorks2008R2 SET RECOVERY BULK_LOGGED;
GO
-- Put your SELECT INTO statement here
GO
ALTER DATABASE AdventureWorks2008R2 SET RECOVERY FULL;

这也需要批量插入如果您希望对事务日志的影响微乎其微作为的优化大容量导入性能（MSDN）：

This is also required for bulk inserts if you wish to have minimal impact on the transaction log as described in Optimizing Bulk Import Performance (MSDN):

对于完整恢复模式下的数据库，在批量导入所执行的所有行插入操作完全记录在事务日志中。对于大数据的进口，这可能会导致事务日志迅速填满。对于大容量导入操作，最小的日志记录比完整记录更有效，减少了一个大容量导入操作填满日志空间的可能性。 要最低限度地登录上通常使用完整恢复模式的数据库批量导入操作，可以先将数据库切换到大容量日志恢复模式。批量导入数据后，切换恢复模式回完整恢复模式。

For a database under the full recovery model, all row-insert operations that are performed during bulk import are fully logged in the transaction log. For large data imports, this can cause the transaction log to fill rapidly. For bulk-import operations, minimal logging is more efficient than full logging and reduces the possibility that a bulk-import operation will fill the log space. To minimally log a bulk-import operation on a database that normally uses the full recovery model, you can first switch the database to the bulk-logged recovery model. After bulk importing the data, switch the recovery model back to the full recovery model.

（重点煤矿）的

即。如果你还没有设置数据库恢复模式 BULK_LOGGED 执行批量插入，然后在您将当前无法获得最小交易的好处登录散装insers无论是等事务日志不会是你减速的来源。 （该 SqlBulkCopy的类不会自动为您做什么的）

I.e. if you don't already set the database recovery model to BULK_LOGGED before performing a bulk insert then you won't currently be getting the benefit of minimal transaction logging with bulk insers either and so the transaction log won't be source of your slowdown. (The SqlBulkCopy class doesn't do this for you automatically or anything)

这篇关于BulkCopy从存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！