I have two tables in a 1-to-many relationship, with the parent having an "identity" (auto-numbered) primary key. I would like to copy some of the joined records back into the same tables (which means that new auto-numbered ID's are generated for the copies). However, as far as I know, such a copy would require a SQL "insert," and an "insert" only works on one table at a time. To make matters worse, once a "copy" of the parent records has been made, I would need a way to correlate the new "identity" (auto-numbered) ID's with the old ID's, to ascertain which records to copy/insert in the child table.

All of this leaves me with the impression that there is not a way to do this with a raw SQL command. Instead, I'll need a stored procedure with looping logic and variables - an outright little program - to accomplish this. Is this correct, or is there a fancy SQL expression that achieves the goal?

Could you post table structure, sample data and expected output?

Harsh Athalye
India
"Nothing is impossible"

This can be accomplished with SQL expressions as well as procedures, however with the sql expressions you would have to make several passes creating a temp-result-table. The questions I have are: how do u identify which parent record to duplicate; and once the parent record and child recs ascertained and merged/appended to the original table...then does this table replace the original?

OR

Have you resolved this already?