JDBC, INSERT MULTIPLE records obtained from query, into other table

Question

I need to insert multiple records into mysql database at once. What I am doing is SELECT on one table, from my application with Prepared statement. What I must do is to insert those results into one other temporary table.

String sqlquery = "select * from table1 where arg1 = ?";
PreparedStatement statement;
statement = con.prepareStatement(sql);


ArrayList<String>termlist = index.getTermList();
for (int i = 0; i < termlist.size(); i++) 
{
    String search = termlist.get(i);
    statement.setString(1,search);

    ResultSet rs1 = statement.executeQuery(); //
}

These sets might be big. What is the fastest way to insert this ResultSet somehow into my other table, after each iteration?

How to pass this resultsset to some stored procedure, if there is way?

Answer 1

You could perform a single query if you pass a comma separated list of parameter values into a stored procedure and use an IN (val1, val2, ...) clause. Not sure what MySQL's limit is for the length of an IN clause.

Here's a split implementation.

Answer 2

You could get the database to do the work for you:

INSERT INTO othertable
SELECT col1, col2 FROM table1
WHERE arg1 = ?

This way you don't have to send a copy of data to the database that already exists there.