Batch insert on MySQL is possible

[Shreevats]

I am student where i need to know that is it possible to do a batch insert on MySQL ? As i am not much familiar about this could any one of tell me that isit possible or not? If yes how does one go about it?

[Baltazar]

Yes, Batch insert on MySQL is possible. To do this, put the commands you want to run in a file, then tell mysql to read its input from the file:

shell> mysql < batch-file

If you are running mysql under Windows and have some special characters in the file that cause problems, you can do this:

C:\> mysql -e "source batch-file"

If you need to specify connection parameters on the command line, the command might look like this:

shell> mysql -h host -u user -p < batch-file
Enter password: ********

If you are attempting to use a batch file make sure that you start 'mysql' with the '--default-character-set=utf8' option or you will end up with whatever the server default is.

[MattyBoy]

Now In MySQL it is also possible to fix problem for cases when number of rows in auto-increment batch insert is not known as we do not have to allocate sequential auto increment values if row level replication is used.You can even to Batch Update using MySql, heres how:-

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class MySqlUpdate {

private static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/test";
String usrname = "root";
String passwd = "";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, usrname, passwd);
return conn;
}

public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = getConnection();
conn.setAutoCommit(false);
String query = "insert into test(name, desg) values(?, ?)";
ps = conn.prepareStatement(query);
ps.setString(1, "ABC");
ps.setString(2,"Software Engineer");
ps.addBatch();

ps.setString(1, "XYZ");
ps.setString(2,"Sr. Software Engineer");
ps.addBatch();

ps.setString(1, "PQR");
ps.setString(2,"Team Leader");
ps.addBatch();

int[] updateCounts = ps.executeBatch();
System.out.println("Length:" + updateCounts.length);
conn.commit();
} catch (BatchUpdateException e) {
try {
conn.rollback();
} catch (Exception e2) {
e.printStackTrace();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
pstmt.close();
conn.close();
}
}
}

[Shreevats]

Thanks for the help and replies. I think thats enough for what i was in serach. Thank you