Insert of Join
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?