How to do a batch insert in MySQL
MysqlSqlDatabaseInsertMysql Problem Overview
I have 1-many number of records that need to be entered into a table. What is the best way to do this in a query? Should I just make a loop and insert one record per iteration? Or is there a better way?
Mysql Solutions
Solution 1 - Mysql
From the MySQL manual
> INSERT statements that use VALUES > syntax can insert multiple rows. To do > this, include multiple lists of column > values, each enclosed within > parentheses and separated by commas. > Example:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
Solution 2 - Mysql
Most of the time, you are not working in a MySQL client and you should batch inserts together using the appropriate API.
E.g. in JDBC:
connection con.setAutoCommit(false);
PreparedStatement prepStmt = con.prepareStatement("UPDATE DEPT SET MGRNO=? WHERE DEPTNO=?");
prepStmt.setString(1,mgrnum1);
prepStmt.setString(2,deptnum1);
prepStmt.addBatch();
prepStmt.setString(1,mgrnum2);
prepStmt.setString(2,deptnum2);
prepStmt.addBatch();
int [] numUpdates=prepStmt.executeBatch();
Solution 3 - Mysql
Insert into table(col1,col2) select col1,col2 from table_2;
Please refer to MySQL documentation on INSERT Statement
Solution 4 - Mysql
Load data infile query is much better option but some servers like godaddy restrict this option on shared hosting so , only two options left then one is insert record on every iteration or batch insert , but batch insert has its limitaion of characters if your query exceeds this number of characters set in mysql then your query will crash , So I suggest insert data in chunks withs batch insert , this will minimize number of connections established with database.best of luck guys
Solution 5 - Mysql
mysql allows you to insert multiple rows at once INSERT manual