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?...
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:
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With