Tell me, how can I insert a large number of records at once from the C # code?

I know that there is BulkCopy, but are there any other options?

There is an option to generate VALUES and insert 1000 records, but will it not be slow? After all, a large string with a request can be sent to the server (Maybe I want to insert a hefty XML).

UPD

Found another option: http://www.sqlservercentral.com/blogs/nycnet/2012/05/11/one-way-to-insert-many-rows-very-fast-from-your-net-application/

The XML from which the insertion occurs is sent to the server to the procedure.

In theory, this allows you to insert over 1000 entries, but will not performance suffer?

  • using BulkCopy I inserted about 200k records once a minute. Another option BULK INSERT from a pre-prepared file. The difference in time within the measurement error. How often are you going to do a bulk insert? If less than once a minute, then in performance you are unlikely to be rested. But to insert lines one by one in a loop through LiNQ requests, with such a number, do not even think the server will be comfortable, but I did not wait for the completion of the task, I stopped forcibly. - rdorn
  • Most likely, with values appreciable time will be spent on parsing the query text and compiling it. I think that insertion from XML will be faster than values (except, perhaps, for the case when a very small number of records are inserted), and BulkCopy will be faster than XML-method. - i-one

1 answer 1

In SqlCommand you can pass a decent-sized script, you can even in the parameters.

  • How decent? Here, for example, values ​​imposes a limit of 1000 entries and all decentness disappears ... Another thing is to send xml on with the data for insertion, as I described in the question, but how fast is it? - iluxa1810
  • What prevents to pass N insert instructions, in which there will be 1000 values ​​each. And on the merits, the CommandTest can contain up to 2,147,483,647 characters. - Evgeny Veprikov