Copying the Data
After creating a table in the target database, you need to copy data to it. To do that you must create both SELECT
statements. The SELECT
will read the table data from the source database. The INSERT
statement will write the data to the target database.
First create three StringBuffer instances to hold the SELECT
statements as well as one to hold the VALUES
clause of the INSERT
StringBuffer selectSQL = new StringBuffer();
StringBuffer insertSQL = new StringBuffer();
StringBuffer values = new StringBuffer();
The data mover retrieves the columns, displaying the status for each.
Collection<String> columns =
System.out.println("Begin copy: " + table);
Next it constructs the beginnings of the SELECT
insertSQL.append("INSERT INTO ");
Then it loops over all the columns to create the SELECT
statements, inserting commas after all but the first column name
boolean first = true;
for (String column : columns)
first = false;
It adds the column name to each statement, and a question mark character (?
) to the VALUES
clause. The question mark is necessary because you're using parameterized SQL; you'll replace that with the actual value later. Creating the statement this way lets the database pre-compile the INSERT
statement to save time.
Next append the FROM
selectSQL.append(" FROM ");
insertSQL.append(") VALUES (");
At this point you have completed the SELECT
statements, so you can execute the SELECT
statement and get a list of records.
// now copy
PreparedStatement statement = null;
ResultSet rs = null;
statement = target.prepareStatement(
rs = source.executeQuery(selectSQL.toString());
The sample keeps a count of rows for status reporting.
int rows = 0;
You can now loop over all the records to complete the INSERT
statements, setting the values as you go.
For each INSERT
statement copy in the individual column data.
for (int i = 1; i <= columns.size(); i++)
Finally, execute the INSERT
The sample code completes by displaying status information.
System.out.println("Copied " + rows + " rows.");
As described here, this example is a simple utility to copy data from one database to the other, but I often use it as a foundation for larger database utilities. You'll find that it contains many of the functions that you'll need to create more complex data-copying applications.