How to copy a table from one database to another?

Question

I need to take a table from one database and load it into another database. So, I create two separate connections. 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? Bottom line , how to reuse a prepared result set

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

Answer 1

The easiest way to do this is to create a prepared report for insertion. It allows you to create one operator object that can be used to run a 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(); } } } 

Rows are inserted into table2 when repeating the results from table1 , so there is no need to store the entire result set.

You can also use prepared addBatch() and executeBatch() instructions to queue all insertions and send them to the database at the same time, instead of sending a separate message to the database for each individual row inserted. But this forces JDBC to hold all pending inserts in memory locally, which seems to be an attempt to avoid. Thus, in this case, you can bet on one line at a time.