1

I can duplicate rows from a single table using INSERT and SELECT whilst assigning a new auto increment value eg:

INSERT INTO tableA (tableA_ID,tableB_ID,col1,col2)
SELECT '0',tableB_ID,col1,col2 WHERE tableB_ID='1'

However, suppose I have 2 tables with autoincrement ids, but, whose data I wish to duplicate simultaneously.

Please consider these tables;

report
+----------------------+---------------+------+-----+
| Field                | Type          | Null | Key |
+----------------------+---------------+------+-----+
| report_id            | int(11)       | NO   | PRI |
| report_title         | varchar(255)  | NO   |     |
+----------------------+---------------+------+-----+

document
+----------------------+---------------+------+-----+
| Field                | Type          | Null | Key |
+----------------------+---------------+------+-----+
| doc_id               | int(11)       | NO   | PRI |
| report_id            | int(11)       | NO   |     |
| document_title       | varchar(255)  | NO   |     |
+----------------------+---------------+------+-----+

document_asset
+----------------------+---------------+------+-----+
| Field                | Type          | Null | Key |
+----------------------+---------------+------+-----+
| asset_id             | int(11)       | NO   | PRI |
| doc_id               | int(11)       | NO   |     |
| report_id            | int(11)       | NO   |     |
| asset_name           | varchar(255)  | NO   |     |
+----------------------+---------------+------+-----+

It is necessary to duplicate a report, it's documents and their assets so that they may be edited whilst preserving the original. I can using the aforementioned method, duplicate tables 'report' and 'document' without undue stress. However, I am unable to create query that will allow me to duplicate the document and also the document_asset(s) whilst linking the duplicate assets to the duplicate parent document.

Here is an example of the initial data:

Report:

+-----------+--------------------------+
| report_id | report_title             |
+-----------+--------------------------+
|         1 | Report A                 |
+-----------+--------------------------+

Document:

+-----------+--------------------------+
| doc_id | report_id | document_title  |
+-----------+--------------------------+
|      1 |         1 | Doc 1           |
|      2 |         1 | Doc 2           |
+-----------+--------------------------+

Document Asset:

+-----------+--------------------------------+
| asset_id | doc_id | report_id | asset_name |
+-----------+--------------------------------+
|        1 |      1 |         1 | Asset 1    |
|        2 |      1 |         1 | Asset 2    |
|        3 |      2 |         1 | Asset 3    |
+-----------+--------------------------------+

This is the output I am working towards:

Report Output:

+-----------+--------------------------+
| report_id | report_title             |
+-----------+--------------------------+
|         1 | Report A                 |
|         2 | Copy of Report A         |
+-----------+--------------------------+

Document Output:

+-----------+--------------------------+
| doc_id | report_id | document_title  |
+-----------+--------------------------+
|      1 |         1 | Doc 1           |
|      2 |         1 | Doc 2           |
|      3 |         2 | Doc 1           |
|      4 |         2 | Doc 2           |
+-----------+--------------------------+

Document Asset Output:

+-----------+--------------------------------+
| asset_id | doc_id | report_id | asset_name |
+-----------+--------------------------------+
|        1 |      1 |         1 | Asset 1    |
|        2 |      1 |         1 | Asset 2    |
|        3 |      2 |         1 | Asset 3    |
|        4 |      3 |         2 | Asset 1    |
|        5 |      3 |         2 | Asset 2    |
|        6 |      4 |         2 | Asset 3    |
+-----------+--------------------------------+

Note, duplicating the report is easy enough, it is duplicating documents and document assets that is the issue.

Bernie Davies
  • 419
  • 6
  • 15
  • 1
    How should your output table(s) look like? I don't think I fully understand your question. – Tin May 25 '16 at 21:18
  • I need to duplicate a report, its documents and assets into the same tables. Eg. 'Report A' would be duplicated as 'Copy of Report A' and would have a new ID. If 'Report A' has documents those will be copied and assigned new ID linking to 'Copy of Report A', likewise with the document assets - which would need to be related to their respective duplicates. – Bernie Davies May 26 '16 at 09:06
  • 1
    In absence of OUTPUT clause, the only way I can think of doing this "deep copy" is to use nested loops / cursors. – Alex May 26 '16 at 10:33
  • TY. Could this be worth investigation: http://stackoverflow.com/questions/5469416/mysql-insert-with-output-like-mssql – Bernie Davies May 26 '16 at 10:59
  • 1
    It seems that you cannot insert records in order either (http://stackoverflow.com/questions/14853597/order-by-ignored-when-inserting-into-mysql-table). Inserting records 1 by 1 in a cursor loops looks like the only option. – Alex May 26 '16 at 11:21
  • 1
    @Bernie Davies, "SELECT LAST_INSERT_ID();" will have to be used in cursors when inserting one record at a time. Unfortunatly there is no way that I can find to return multiple IDs from the last insert (similar to what SQL Server OUTPUT does). – Alex May 26 '16 at 11:30

0 Answers0