Duplicating parent, child and grandchild records

Question

I have a parent table that represents a document of-sorts, with each record in the table having n children records in a child table. Each child record can have n grandchild records. These records are in a published state. When the user wants to modify a published document, we need to clone the parent and all of its children and grandchildren.

The table structure looks like this:

Parent

CREATE TABLE [ql].[Quantlist] (
    [QuantlistId]   INT           IDENTITY (1, 1) NOT NULL,
    [StateId]       INT           NOT NULL,
    [Title]         VARCHAR (500) NOT NULL,
    CONSTRAINT [PK_Quantlist] PRIMARY KEY CLUSTERED ([QuantlistId] ASC),
    CONSTRAINT [FK_Quantlist_State] FOREIGN KEY ([StateId]) REFERENCES [ql].[State] ([StateId])
);

Child

CREATE TABLE [ql].[QuantlistAttribute]
(
    [QuantlistAttributeId] INT IDENTITY (1, 1),
    [QuantlistId] INT NOT NULL,
    [Narrative] VARCHAR (500) NOT NULL,
    CONSTRAINT [PK_QuantlistAttribute] PRIMARY KEY ([QuantlistAttributeId]), 
    CONSTRAINT [FK_QuantlistAttribute_QuantlistId] FOREIGN KEY ([QuantlistId]) REFERENCES [ql].[Quantlist]([QuantlistId]),
)

Grandchild

CREATE TABLE [ql].[AttributeReference]
(
    [AttributeReferenceId] INT IDENTITY (1, 1),
    [QuantlistAttributeId] INT NOT NULL,
    [Reference] VARCHAR (250) NOT NULL,
    CONSTRAINT [PK_QuantlistReference] PRIMARY KEY ([AttributeReferenceId]), 
    CONSTRAINT [FK_QuantlistReference_QuantlistAttribute] FOREIGN KEY ([QuantlistAttributeId]) REFERENCES [ql].[QuantlistAttribute]([QuantlistAttributeId]),
)

In my stored procedure, i pass in the QuantlistId I want to clone as @QuantlistId. Since the QuantlistAttribute table has a ForeignKey I can easily clone that as well.

INSERT INTO [ql].[Quantlist] (
    [StateId],
    [Title],
) SELECT 
    1,
    Title,
    FROM [ql].[Quantlist]
    WHERE QuantlistId = @QuantlistId

SET @ClonedId = SCOPE_IDENTITY()

INSERT INTO ql.QuantlistAttribute(
        QuantlistId
        ,Narrative)
    SELECT 
        @ClonedId,
        Narrative,
    FROM ql.QuantlistAttribute
    WHERE QuantlistId = @QuantlistId

The trouble comes down to the AttributeReference. If I cloned 30 QuantlistAttribute records, how do I clone the records in the reference table and match them up with the new records I just inserted in to the QuantlistAttribute table?

    INSERT INTO ql.AttributeReference(
            QuantlistAttributeId,
            Reference,)
        SELECT 
            QuantlistAttributeId,
            Reference,
        FROM ql.QuantlistReference
        WHERE ??? I don't have a key to go off of for this.

I thought I could do this with some temporary linking tables that holds the old attribute id's along with the new attribute id's. I don't know how to go about inserting the old Attribute Id's in to a temp table along with their new ones. Inserting the existing Attributes, by QuantlistId, is easy enough, but I can't figure out how to make sure I link the correct new and old Id's together in some way, so that the AttributeReference table can be cloned right. If I could get the QuantlistAttribute new and old Id's linked, I could join on that temp table and figure out how to restore the relationship of the newly cloned references, to the newly cloned attributes.

Any help on this would be awesome. I've spent the last day and a half trying to figure this out with no luck :/

Please excuse some of the SQL inconsistencies. I re-wrote up the sql real quick, trimming out a lot of additional columns, related-tables and constraints that weren't needed for this question.

Edit

After doing a little digging around, I found that OUTPUT might be useful for this. Is there a way to use OUTPUT to map the QuantlistAttributeId records I just inserted, to the QuantlistAttributeId they originated from?

Answer 1

You can use OUTPUT to get the inserted rows.

1. You can insert the data into QuantlistAttribute based on the order of ORDER BY c.QuantlistAttributeId ASC

2. Have a temp table/table variable which 3 columns

   • an id identity column
   • new QuantlistAttributeId
   • old QuantlistAttributeId.

3. Use OUTPUT to insert new identity values of QuantlistAttribute into a temp table/table variable. The new IDs are generated in the same order as c.QuantlistAttributeId

4. Use a row_number() ordered by QuantlistAttributeId to match the old QuantlistAttributeId and new QuantlistAttributeIds based on row_number() and id of the table variable and update the values or old QuantlistAttributeId in the table variable

5. Use the temp table and join with AttributeReference and insert records in one go.

Note: ORDER BY during INSERT INTO SELECT and ROW_NUMBER() to get matching old QuantlistAttributeId is required because looking at your question, there seems to be no other logical key to map old and new records together.

Query for above Steps

DECLARE @ClonedId INT,@QuantlistId INT = 0

INSERT INTO [ql].[Quantlist] (
    [StateId],
    [Title]
) SELECT 
    1,
    Title
    FROM [ql].[Quantlist]
    WHERE QuantlistId = @QuantlistId

SET @ClonedId = SCOPE_IDENTITY()
--Define a table variable to store the new QuantlistAttributeID and use it to map with the Old QuantlistAttributeID
DECLARE @temp TABLE(id int identity(1,1), newAttrID INT,oldAttrID INT)

INSERT INTO ql.QuantlistAttribute(
        QuantlistId
        ,Narrative)
        --New QuantlistAttributeId are created in the same order as old QuantlistAttributeId  because of ORDER BY
        OUTPUT inserted.QuantlistAttributeId,NULL INTO @temp
    SELECT 
        @ClonedId,
        Narrative
    FROM ql.QuantlistAttribute c
    WHERE QuantlistId = @QuantlistId
    --This is required to keep new ids generated in the same order as old
    ORDER BY c.QuantlistAttributeId ASC

    ;WITH CTE AS
    (
        SELECT c.QuantlistAttributeId,
        --Use ROW_NUMBER to get matching id which is same as the one generated in @temp
        ROW_NUMBER()OVER(ORDER BY c.QuantlistAttributeId ASC) id
        FROM ql.QuantlistAttribute c
        WHERE QuantlistId = @QuantlistId
    )
    --Update the old value in @temp 
    UPDATE T
    SET oldAttrID = CTE.QuantlistAttributeId
    FROM @temp T
    INNER JOIN CTE ON T.id = CTE.id


INSERT INTO ql.AttributeReference(
            QuantlistAttributeId,
            Reference)
        SELECT 
            T.NewAttrID,
            Reference
        FROM ql.AttributeReference R
        --Use OldAttrID to join with ql.AttributeReference and insert NewAttrID
        INNER JOIN @temp T
        ON T.oldAttrID = R.QuantlistAttributeId

Hope this helps.