Fastest way to insert many rows

Question

I have a DataTable which contains hundreds of thousands of rows. Through the course of my procedure, I am adding a few thousand rows to this DataTable, which need to be added to the database too. Rather than creating an INSERT statement for each record, I would like to insert them as quickly as possible. The MySQL LOAD INTO command is not suitable, as I do not want to involve any external CSV files.

What I have done so far, is use a MySqlDataAdapter and call the 'Update' method with only the insertion changes, like so:

MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(adapter);
adapter.InsertCommand = commandBuilder.GetInsertCommand();
adapter.Update(myDataTable);

This is also running painfully slow, so I suspect that they are being inserted one row at a time, too. What options do I have? Is building a long INSERT statement with all the values included in it, the only way to go?

Answer 1

Insert values like that :-

INSERT INTO tbl_name
    (a,b,c)
VALUES
    (1,2,3),
    (4,5,6),
    (7,8,9);

To optimize insert speed, combine many small operations into a single large operation.