Menu
I have previously used the SQLBulkCopy class to load data into a MS SQL Server db. The results were very good, and worked exactly as I intended it to.
Now, I'm trying to use a script task in SSIS to bulk load data into a MySQL (5.5.8) database using either an ODBC or ADO.NET connection (recommend?).
The columns in my dataset correspond with the columns of the MySQL table. What is the best way to do a bulk insert of a dataset into a MySQL database?
258
MySQL INSERT multiple rows statement In this syntax: First, specify the name of table that you want to insert after the INSERT INTO keywords. Second, specify a comma-separated column list inside parentheses after the table name. Third, specify a comma-separated list of row data in the VALUES clause.
Syntax to insert bulk data in MySQL The general syntax of inserting bulk values in a table in MySQL is: INSERT INTO table_name VALUES (data), (data), (data); The explanation to the above general syntax is simple: Type the clause INSERT INTO and the table name in which you want to insert the data.
To do a batch insert, we need to use all column names with parenthesis, separated by ','. Let us see an example. First, we will create a table. The following is the CREATE command to create a table.
To optimize insert speed, combine many small operations into a single large operation. Ideally, you make a single connection, send the data for many new rows at once, and delay all index updates and consistency checking until the very end.
You can use the MySqlBulkLoader shipped with the MySQL Connector for .NET:
var bl = new MySqlBulkLoader(connection);
bl.TableName = "mytable";
bl.FieldTerminator = ",";
bl.LineTerminator = "\r\n";
bl.FileName = "myfileformytable.csv";
bl.NumberOfLinesToSkip = 1;
var inserted = bl.Load();
Debug.Print(inserted + " rows inserted.");
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
