What is the best way to copy data from related tables to another related tables?

What is the best way to copy data from related tables to another related tables with same schema. Table are connected with one-to-many relationship.

Consider following schema

firm

id | name | city.id (FK)

employee

id | lastname | firm.id (FK)

firm2

id | name | city_id (FK)

employee2

id | lastname |firm2.id (FK)

What I want to do is to copy rows from firm with specific city.id to firm2 and and their employees assosiated with firm to table employee2.

I use posgresql 9.0 so I have to call SELECT nextval('seq_name') to get new id for table.

Right now I perform this query simply iterating over all rows in Java backend server, but on huge amount of data (50 000 employee and 2000 of firms) it takes too much time ( 1-3 minutes).

I'm wondering is there another more tricky way to do it, for 开发者_C百科example select data into temp table? Or probably use store procedure and iterate over rows with cursror to avoid buffering on my backend server?

This is one problem caused by simply using a sequence or identity value as your sole primary key in a table.

If there is a real-life unique index/primary key, then you can join on that. The other option would be to create a mapping table as you fill in the tables with sequences then you can insert into the children tables' FKs by joining to the mapping tables. It doesn't completely remove the need for looping, but at least some of the inserts get moved out into a set-based approach.