Insert 1 million rows into MySQL Server quickly

Question

I am trying to insert 2 Milion Rows to MySql database in a single commit. But I could not find better solutions for this operation. For the MsSql I found the following solution.

        SqlBulkCopy objbulk = new SqlBulkCopy(conn1);
        //assign Destination table name  
        objbulk.DestinationTableName = "WinerDetails";

        objbulk.ColumnMappings.Add("Ref_ID", "Ref_ID");
        objbulk.ColumnMappings.Add("Region_ID", "Region_ID");
        conn1.Open();
        //insert bulk Records into DataBase.  
        objbulk.WriteToServer(tbl);
        conn1.Close();

Is there any similar method to insert data in MySQL as above??

Does this answer your question? [How to do a batch insert in MySQL](https://stackoverflow.com/questions/5526917/how-to-do-a-batch-insert-in-mysql) — David Brossard, Mar 22 '21 at 05:18
See also https://dev.mysql.com/doc/refman/5.6/en/optimizing-innodb-bulk-data-loading.html — David Brossard, Mar 22 '21 at 05:19
LOAD DATA is the most fast data loading method in MySQL. If any indices/constraints exists on the table (except PK) then in most cases dropping indices/triggers and disabling checks with future re-creation is faster (but this must be tested in each separate case). — Akina, Mar 22 '21 at 05:27

score 1 · Answer 1 · edited Mar 26 '21 at 19:32

Thanks for your support. I have found the following method and it's working fine for me.

public void BulkInsertMySQL(DataTable table, string tableName) {
     using (conn1)
     {
            conn1.Open();

            using (MySqlTransaction tran = conn1.BeginTransaction(IsolationLevel.Serializable))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    cmd.Connection = conn1;
                    cmd.Transaction = tran;
                    cmd.CommandText = "SELECT Tran_ID, BranchID, ATM_ID, Amount, FileType, TranDate FROM " + tableName + " LIMIT 0";

                    using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
                    {
                        //  adapter.UpdateBatchSize = 10000;
                        using (MySqlCommandBuilder cb = new MySqlCommandBuilder(adapter))
                        {
                            cb.SetAllValues = true;
                            adapter.Update(table);
                            tran.Commit();
                        }
                    };
                }
            }
        }
     }

Special thanks for This Answer

Note that LIMIT without ORDER BY is fairly meaningless – Strawberry Mar 22 '21 at 07:19 — Strawberry, Mar 22 '21 at 07:19

Insert 1 million rows into MySQL Server quickly

1 Answers1