Dot NET Dot NET - 2 months ago 13
MySQL Question

Fastest way to insert many rows

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

The only solution which I see is:

1) Convert DataTable to csv-> you can google it.

2) Save it on server side in temp directory.

3)Using MySqlBulkLoader here is link to article about it. Load the file saved in the temp directory.

4) After that delete the file from the temp directory.