SQLCommand ExecuteNonQuery Maximum CommandText Len

Question

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!

Answer 1

It depends on the SQL Server version.

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

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

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.

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

Answer 2

A brief bing search showed me this link: http://dbaspot.com/sqlserver-programming/399616-what-max-length-sqlcommand-commandtext.html

It says:

The maximum size for a query batch is 65536 * network packet size. The default packet is 4096 bytes, which gives an upper limit of 268 million bytes.

It's unclear if that changes for different versions of sql server though.