C# sql-server-2008 sql-server bulkinsert

如何将数据行流式传输到sqlserver？

发布于 2024-11-28 18:16:40 字数 938 浏览 1 评论 0原文

我需要从一个数据库复制大型结果集并将其保存到另一个数据库。

由于保存过程中涉及一些逻辑，存储过程可用于获取和存储。

我正在尝试找到一种有效的解决方案，但我无法将整个数据集保存在内存中，并且我想最大程度地减少往返次数。

从源表中读取数据

var reader = fetchCommand.ExecuteReader();
while (reader.Read()){...}

是否有一种方法可以将此数据插入到另一个 sqlCommand 中，而无需将整个数据集加载到 DataTable 中，也无需逐行插入行？

Sqlserver 是源数据库和目标数据库上的 MS SQL Server 2008。数据库位于不同的服务器上。不能使用 SSIS 或链接服务器。

编辑： 看来可以使用表值参数将行流式传输到存储过程。也将研究这种方法。

更新： 是的，可以将数据从 command.ExecuteReader 流式传输到另一个命令，如下所示：

var reader = selectCommand.ExecuteReader();
insertCommand.Parameters.Add(
    new SqlParameter("@data", reader)
        {SqlDbType = SqlDbType.Structured}
    );

insertCommand.ExecuteNonQuery();

其中 insertCommand 是带有表值参数 @data 的存储过程>。

原文

I need to copy large resultset from one database and save it to another database.

Stored procedures are used for both fetching and storing due to the fact that there is some logic involved during saving.

I'm trying to find an efficent solution, no way I can hold the whole dataset in memory, and I would like to minimize roundtrips count.

Data is read from source table with

var reader = fetchCommand.ExecuteReader();
while (reader.Read()){...}

Is there a way to insert this data to another sqlCommand without loading the whole dataset into a DataTable but also without inserting rows ine by one?

Sqlserver is MS SQL Server 2008 on both source and target databases. Databases are on different servers. Use of SSIS or linked servers is not an option.

EDIT:
It appears it's possible to stream rows into a stored procedure using table-valued paramaters. Will investigate this approach as well.

UPDATE:
Yes it's possible to stream data out from command.ExecuteReader to another command like this:

var reader = selectCommand.ExecuteReader();
insertCommand.Parameters.Add(
    new SqlParameter("@data", reader)
        {SqlDbType = SqlDbType.Structured}
    );

insertCommand.ExecuteNonQuery();

Where insertCommand is a stored procedure with table-valued parameter @data.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

请帮我爱他 2024-12-05 18:16:40

您需要SqlBulkCopy。您可以像这样使用它：

using (var reader = fetchCommand.ExecuteReader())
using (var bulkCopy = new SqlBulkCopy(myOtherDatabaseConnection))
{
  bulkCopy.DestinationTableName = "...";
  bulkCopy.ColumnMappings = ...
  bulkCopy.WriteToServer(reader);
}

还有一个属性可以设置批量大小。像 1000 行这样的数据可能会为您提供内存使用和速度之间的最佳权衡。

尽管这不允许您将其通过管道传输到存储过程中，但最好的方法可能是将数据复制到临时表，然后在服务器上运行批量更新命令以将数据复制到其最终位置。到目前为止，这通常比为每一行执行大量单独的语句要快得多。

You need SqlBulkCopy. You can just use it like this:

using (var reader = fetchCommand.ExecuteReader())
using (var bulkCopy = new SqlBulkCopy(myOtherDatabaseConnection))
{
  bulkCopy.DestinationTableName = "...";
  bulkCopy.ColumnMappings = ...
  bulkCopy.WriteToServer(reader);
}

There is also a property to set the batch size. Something like 1000 rows might give you the best trade-off between memory usage and speed.

Although this doesn't let you pipe it into a stored procedure, the best approach might be to copy data to a temporary table and then run bulk update command on the server to copy the data into its final location. This usually faster by far than executing lots of separate statements for each row.

回复收藏 0 原文