Copying Relational Table Data

Question

I hope I'm not being too long winded in this question, I just want to make sure that what I'm asking is completely clear (I think it's rather confusing :).

I've got a database with a bunch of tables with all my foreign key constraints set up. Relations are sometimes several tables deep, and there are also cases where a child is related to more than one parent table. I want to insert a copy of one of my "top level" table rows, and all of child table data that relates to it (keeping the relational integrity). That is to say my new top level row gets its own new primary key (via auto_increment), and all the new child rows get their own primary keys (again through auto_increment), and all the foreign key information of the tables relates analogously to the data I copied (only now with the newly created primary keys). So now I would have a copy of the relational data that was independently mutable from the original.

To make my example more concrete, I have painstakingly set up an analogous, but simpler example. Lets define the following tables:

alt text http://www.freeimagehosting.net/uploads/ef22070a89.png

All the green id fields are auto_update primary keys, and the yellowish ones are indexed columns with foreign key constraints. Lets say the database has the following data in it initially:

job_types
+----+----------+
| id | jobcode  |
+----+----------+
|  1 | DEADBEEF |
|  3 | FEEDFACE |
+----+----------+

managers
+----+---------------+-------------+
| id | name          | job_type_id |
+----+---------------+-------------+
|  1 | John          |           1 |
|  3 | Michael Scott |           3 |
+----+---------------+-------------+

departments
+----+------+------------+
| id | name | manager_id |
+----+------+------------+
|  1 | H32  |          1 |
|  2 | X11  |          3 |
+----+------+------------+

employees
+----+-------------+---------------+------------+-------------+
| id | name        | department_id | manager_id | job_type_id |
+----+-------------+---------------+------------+-------------+
|  1 | Billy Bob   |             1 |          1 |           1 |
|  2 | Sandra Lee  |             1 |          1 |           3 |
|  3 | Buddy Holly |             2 |          3 |           1 |
+----+-------------+---------------+------------+-------------+

Now say what I want to do is make a relational copy of department H32 (id=1).

What I should end up with is something like the following (obviously actual values of primary keys is not important, referential integrity is).

job_types
+----+----------+
| id | jobcode  |
+----+----------+
|  1 | DEADBEEF |
|  3 | FEEDFACE |
|  4 | DEADBEEF |
|  5 | FEEDFACE |
+----+----------+

managers
+----+---------------+-------------+
| id | name          | job_type_id |
+----+---------------+-------------+
|  1 | John          |           1 |
|  3 | Michael Scott |           3 |
|  4 | John          |           4 |
+----+---------------+-------------+

departments
+----+------+------------+
| id | name | manager_id |
+----+------+------------+
|  1 | H32  |          1 |
|  2 | X11  |          3 |
|  3 | H32  |          4 |
+----+------+------------+

employees
+----+-------------+---------------+------------+-------------+
| id | name        | department_id | manager_id | job_type_id |
+----+-------------+---------------+------------+-------------+
|  1 | Billy Bob   |             1 |          1 |           1 |
|  2 | Sandra Lee  |             1 |          1 |           3 |
|  3 | Buddy Holly |             2 |          3 |           1 |
|  4 | Billy Bob   |             3 |          4 |           4 |
|  5 | Sandra Lee  |             3 |          4 |           5 |
+----+-------------+---------------+------------+-------------+

What's the most efficiently performing way to implement this type of copy operation? For what it's worth I'm working with MySQL, using the InnoDB table engine, in the context of Grails. I'm looking forward to hearing some good ideas on how you do something like this "the right way."

-- Regards, Vic

I've posted a MySQLDump of the example initialization on PasteBin.

EDIT For what it's worth, I posted a much simpler / broader question here and I got generally positive responses, suggesting that I'm not "just doing it wrong"...

Answer 1

I have done a similar thing using INSERT INTO ... SELECT syntax. (The C api also has a MYSQL_OPTION_MULTI_STATEMENTS_ON which you can use to run multiple statements with. or you could use a procedure).

This is the most effective, since you do not have to move data between the client and the server. The value copied was a template to create a new entity. I don't understand why you are doing this with this particiluar data model.