SQLCommand ExecuteNonQuery最大CommandText长度？ [英] SQLCommand ExecuteNonQuery Maximum CommandText Length?

问题描述

我已经在互联网上搜索了，所有内容似乎都是关于单个字段或只写了一个字。我有一个迁移工具，它可以从旧的遗留数据库（超数据库）迁移到我们的SQL Server DB（2008）。目前，我正在从旧数据库中读取20,000条记录，并生成一个大的 SQLCommand.CommandText 字符串，其中包含以分号分隔的20,000条插入语句。 这很好。但是我可以25k吗？ 30k？我尝试完全没有任何限制，但是当我尝试使用包含超过400万个INSERT的 CommandText 运行 ExecuteNonQuery 时声明，它说的太长了。很抱歉，我不记得确切的错误消息。我找不到有关确切限制的任何文档。对我来说很重要，因为一次可以插入的插入物越多，整个过程就越快。

I've searched around the internet and everything seems to be about individual fields or doing one insert. I have a migration tool that is migrating from an old legacy database (superbase) to our SQL server DB (2008). Currently I'm reading 20,000 records from the old database and generating one big SQLCommand.CommandText string with 20,000 insert statements delimited by a semicolon. This works fine. But can I do 25k? 30k? I tried not having any limit at all, but when I tried to run ExecuteNonQuery with a CommandText containing over 4 million INSERT statements, it said something about being too long. I don't recall the exact error message, sorry. I can't find any documentation on the exact limitations. It's important for me to find out because the more inserts I can fire at a time, the faster the whole process. Any advice / links / documentation would be greatly appreciated, thanks!

推荐答案

这取决于SQL Server版本。

It depends on the SQL Server version.

http://technet.microsoft。 com / en-us / library / ms143432.aspx

对于SQL Server 2012，批处理大小或SQL语句（字符串）= 65,536 *网络数据包大小

For SQL Server 2012, the batch size or SQL Statement (String) = 65,536 * Network packet size.

网络数据包大小是用于在应用程序和关系数据库引擎之间进行通信的表格数据流（TDS）数据包的大小。默认数据包大小为4 KB，由网络数据包大小配置选项控制。

Network Packet Size is the size of the tabular data stream (TDS) packets used to communicate between applications and the relational Database Engine. The default packet size is 4 KB, and is controlled by the network packet size configuration option.

简而言之，64K x 4K = 256 MB。同样，这假设您使用的是默认数据包大小。

In short, 64K x 4K = 256 MB. Again, this assumes you are using default packet sizes.

这篇关于SQLCommand ExecuteNonQuery最大CommandText长度？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！