ADO.NET and SQLBULKCOPY - Maximum write limit for insertion?

Question

Is there a limit on the number of records that ADO.net can insert into the database at the same time? We want to work with sqlbulkcopy , but the developers tell us that ADO does not insert more than 1000 records at a time, so sqlbulkcopy inserting batches of 10,000 records is useless. It's true? Does sqlbulkcopy large batches of 1,000 each up to 10,000?

I don’t think that ADO has the maximum number of entries, but they are developers, so I’m not sure.

Answer 1

This is not true. In fact, you need to install BatchSize if you want it, because the default is 0 or no limit.

Further, having considered the WriteToServerInternal method of the WriteToServerInternal class, this is the only thing that would limit the number of rows sent in one batch.

Finally, even if you sent them in batches of 1000 lines, there is no limit to the total number of lines that can be inserted. Well, with the exception of the memory of a running machine, if it is executed using a DataTable , for example. But if this data is copied from one server to another using SqlDataReader , there is a common limit that could be formed or formed in some way.

Answer 2

 BULKCOPY.BATCHSIZE=1000; 

This will help you fix the bulk copy error.