Is that possible to do bulk copy in mysql.

Question

I need to insert multiple rows in my Mysql database.my rows are available in my dataset.

i am using for loop to send the row one by one is that right way?...

Answer 1

You can insert multiple rows using a single SQL statement like so:

INSERT INTO myTable (col1, col2, col3) VALUES ('myval1', 'myval2', 'myval3'), ('myotherval1', 'myotherval2', 'myotherval3'), ('anotherval1', 'anotherval2', 'anotherval3');

Update:

MarkR is right in his comment - if you're collecting data from a user, or you're compiling information, you can build the query dynamically with something like:

StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("INSERT INTO myTable (col1, col2, col3) VALUES ");
for(int i=0;i<myDataCollection.Count;i++) {
  stringBuilder.Append("(" + myDataCollection[i].Col1 + ", " + myDataCollection[i].Col2 + ", " + myDataCollection[i].Col3 + ")");
  if (i<myDataCollection.Count-1) {
    stringBuilder.Append(", ");
  } else {
    stringBuilder.Append(";");
  }
}

string insertStatement = stringBuilder.ToString();

Two important points to note:

1. If you are accepting input from a user, it is very important to sanitize all user inputs, otherwise malicious users could modify/delete/drop your entire database. For more info, google "SQL Injections."
2. I'm using the StringBuilder class, rather than using a string primitive and simply appending (ie. string s = "Insert..."; s+="blah blah blah") because it's StringBuilder is faster at appending, because it is not treated as an array, and so does not need to resize itself as you append to it.

Answer 2

You can use LOAD DATA INFILE (or LOAD DATA LOCAL INFILE) to bulk load rows into a table from an existing file.

In the case of LOAD DATA LOCAL, the file is transferred from the client to the server and inserted as a big batch. This is not limited by the maximum packet size (as an INSERT is), but it is still limited by the maximum transaction you can roll back in innodb - it all goes in one transaction, so if it's too big, it will exceed your rollback space, and in any case, if you rollback a very large transaction it takes a long time and impacts the server.

Normally it's not advisable to load more than a few (tens maybe, hundreds possibly) megabytes of data as a single insert or LOAD DATA. If you have more, you can split it up.