How to copy table from one database to another?

Question

I need to take a table from one database and upload it to a different database. So, I create two separate connection . Here is my code

Connection connection1 = // set up connection to dbms1
Statement statement = connection1.createStatement();
ResultSet result = statement.executeQuery("select * from ............. "); 

Connection connection2 =  // set up connection to dbms2
// Now I want to upload the ResultSet result into the second database
Statement statement2 = connection2.createStatement("insert into table2 " + result);
statement2.executeUpdate();

The above last lines do not work How can i do this ? The bottomline is how to reuse a ready resultset

ResultSet is a ready java object . I hope there is a way add it to batch or something like this and executeUpdate , but not to write the result set to some temporary space (List, csv etc.) and the insert

Answer 1

The simplest way to do this is with a prepared statement for the insert. It lets you create a single statement object that can be used to run the query multiple times with different parameter values.

try (final Statement statement1 = connection1.createStatement();
     final PreparedStatement insertStatement = 
     connection2.prepareStatement("insert into table2 values(?, ?)"))
{
    try (final ResultSet resultSet =
         statement1.executeQuery("select foo, bar from table1"))
    {
        while (resultSet.next())
        {
            // Get the values from the table1 record
            final String foo = resultSet.getString("foo");
            final int bar = resultSet.getInt("bar");

            // Insert a row with these values into table2
            insertStatement.clearParameters();
            insertStatement.setString(1, foo);
            insertStatement.setInt(2, bar);
            insertStatement.executeUpdate();
        }
    }
}

The rows are inserted into table2 as you iterate through the results from table1, so there's no need to store the whole result set.

You can also use the prepared statement's addBatch() and executeBatch() methods to queue up all the inserts and send them to the database all at once, instead of sending a separate message to the database for each individual inserted row. But that forces JDBC to hold all the pending inserts in memory locally, which it seems you're trying to avoid. So the one-row-at-a-time inserts are your best bet in this case.

Answer 2

If you don't want to manually list out all the field names for every table in the database, you should be able to do this two-step process instead:

1. Copy the table schema using the answer in this question.
2. Use resultSet.getMetaData() to get the list of fields, and use that to drive a modified version of the SELECT/INSERT code in @Wyzard's answer.

I will post code here if I get it working.