SQL How to copy a master record with a unique ID and its relation entry

Question

I have two tables, a master table and a relation table. The Master table as an automatic identity ID field (DOCUMENT_ID) that is generated upon insert. The relation table ties that DOCUMENT_ID from the master table with a USER_NAME entry. I want to be able to create a copy of the the record in the master table and a copy of its relation in the relationship table while establishing the new relation using the new automatically generated DOCUMENT_ID generated by the Master table. Here is an example of my tables and the desired output.

MASTER TABLE:

DOCUMENT_ID |  DOCUMENT_NAME
1           |  Application
2           |  Invoice
3           |  Receipt

RELATION TABLE:

DOCUMENT_ID|       USER_NAME
1          |       John
1          |       Amy
2          |       Jim
2          |       Jane
3          |       John
3          |       Jane

I would like to copy the records from the master table and create a copy of the relationship so that my output looks like this:

MASTER TABLE WITH NEW RECORDS INSERTED: Note that the DOCUMENT_ID column generated the IDs automatically.

DOCUMENT_ID |  DOCUMENT_NAME
1           |  Application
2           |  Invoice
3           |  Receipt  
7           |  Application
8           |  Invoice
9           |  Receipt

RELATION TABLE: This tables needs to tie the relation between the new IDs created in the master table and a copy of the USER_NAMES that were associated with the original DOCUMENT_IDs.

DOCUMENT_ID|       USER_NAME
1          |       John
1          |       Amy
2          |       Jim
2          |       Jane
3          |       John
3          |       Jane
7          |       John
7          |       Amy
8          |       Jim
8          |       Jane
9          |       John
9          |       Jane

Possible duplicate of [How to copy a row and insert in same table with a autoincrement field in MySQL?](https://stackoverflow.com/questions/9156340/how-to-copy-a-row-and-insert-in-same-table-with-a-autoincrement-field-in-mysql) — Elan Hamburger, Jul 18 '17 at 14:10
You may have to use 2 queries (one on the master table, and one on the relationship table), but that solution should work — Elan Hamburger, Jul 18 '17 at 16:18

score 0 · Answer 1 · answered Jul 18 '17 at 21:16

I had no time to test this TSQL but it should give you an idea:

declare @documentID int
declare @newDocumentID int
declare @documentName VARCHAR(256)

declare master_cursor cursor for
select DOCUMENT_ID, DOCUMENT_NAME
from MASTER_TABLE

OPEN master_cursor   
FETCH NEXT FROM master_cursor INTO @documentID, @documentName

WHILE @@FETCH_STATUS = 0   
BEGIN   
    INSERT INTO MASTER_TABLE(DOCUMENT_NAME) VALUES (@documentName)
    -- read the just inserted Document ID
    SELECT @newDocumentID = MAX(DOCUMENT_ID)
    FROM MASTER_TABLE
    WHERE DOCUMENT_NAME = @documentName 

    -- now insert new values in relations table
    INSERT INTO RELATIONS_TABLE
    SELECT @newDocumentID, USER_NAME
    FROM RELATIONS_TABLE
    WHERE
    DOCUMENT_ID = @documentID 

    FETCH NEXT FROM master_cursor INTO @documentID, @documentName
END

SQL How to copy a master record with a unique ID and its relation entry

1 Answers1