Insert data from a table into another table with resulsets-CodePudding

I need to migrate some table's data into another table in the same database;

for example table1:

PersonID    LastName    FirstName   Address  City
1            Foo          Bar        xxx     boh
2            Foo          Bar        xxx     boh
3            Foo          Bar        xxx     boh

and table2

 PersonID   field2  field3  field4   field5
 1          boh     xxx     Foo      Bar
 2          boh     xxx     Foo      Bar
 3          boh     xxx     Foo      Bar

I've tried with this sample code:

ResultSet table1 = s.executeQuery("Select * from table1");
ResultSet table2 = s.executeQuery("Select * from table2");
 
while(table1.next()) {
     table2.insertRow();
}

But with just that instruction I got "The result set is closed." error. Can I insert in table2 the same amount of rows of table1? Then I tought about adding information by reading informations from table1 and adding them to table2 with the resultset updateRow method.

EDIT: Table2 in the beginning should be empty, with just the structure created.

Thanks in advance.

CodePudding user response：

Assuming in Table1 you have PersonId = 1, 2, 3, 4, 5 and in Table2 you have only PersonId = 1, 2 only, if you wish to copy PersonId = 3, 4, 5 from Table1 to Table2, you can achieve this by this query:

INSERT INTO Table2
SELECT t1.PersonId, t1.LastName, t1.FirstName, t1.Address, t1.City
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.PersonId = t2.PersonId
WHERE t2.PersonId IS NULL;

If you wish to get this done from the application, you can just execute it like this:

s.executeQuery(<query>); // <query> is the query I mentioned above.

CodePudding user response：

I've resolved by changing the type of the resultset in resultset.CONCUR_UPDATABLE, and adding this in the while:

while(table1.next()) {
    table2.moveToInsertRow();
    tabella2.updateString("PersonID", table1.getString("PersonID"));
    table2.insertRow();
}