How to do a batch insert in 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);
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();
Insert into table(col1,col2) select col1,col2 from table_2;Please refer to MySQL documentation on INSERT Statement