Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 16-05-2009
Member
 
Join Date: May 2009
Posts: 523
Batch insert on MySQL is possible

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?
Reply With Quote
  #2  
Old 16-05-2009
Member
 
Join Date: May 2008
Posts: 804
Re: Batch insert on MySQL is possible

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:
Quote:
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:
Quote:
C:\> mysql -e "source batch-file"
If you need to specify connection parameters on the command line, the command might look like this:
Quote:
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.
Reply With Quote
  #3  
Old 16-05-2009
Member
 
Join Date: Sep 2005
Posts: 1,427
Re: Batch insert on MySQL is possible

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:-

Quote:
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();
}
}
}
Reply With Quote
  #4  
Old 16-05-2009
Member
 
Join Date: May 2009
Posts: 523
Re: Batch insert on MySQL is possible

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

  TechArena Community > Software > Software Development
Tags: , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Batch insert on MySQL is possible"
Thread Thread Starter Forum Replies Last Post
How to use Batch Files to Create MySQL Database? DANIEL 602 Software Development 4 05-02-2010 07:03 AM
How to Insert data into MySQL table Mahendra varma Software Development 4 04-10-2009 04:42 AM
Insert Images in MySQL from FTP MeteoWatch Software Development 2 02-07-2009 01:06 AM
How to insert GetDate() function in MySQL Shanbaag Software Development 3 18-05-2009 05:40 PM
how to insert a image in mysql cnu0870 Software Development 5 28-04-2009 05:15 PM


All times are GMT +5.5. The time now is 03:36 AM.