Geek Answers Handbook

Is it possible to execute bulk copy in mysql

I need to insert some rows into my Mysql database. My rows are available in my dataset.

I use a loop to send a string one by one, is this the right way? ...

+5
source share
2 answers

You can insert multiple rows using a single SQL statement, for example:

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 collect data from the user or compile the information, you can build the query dynamically using 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:

  • , , , // . google "SQL Injections."
  • StringBuilder (.. string s = "Insert..."; s + = "blah blah blah" ), StringBuilder , .
+3

LOAD DATA INFILE ( LOAD DATA LOCAL INFILE) .

LOAD DATA LOCAL . ( INSERT), - , innodb - , , , , , , .

(, , , ) LOAD DATA. , .

+1

More articles:


All Articles