sql - Duplicating parent, child and grandchild records -

i have parent table represents document of-sorts, each record in table having n children records in child table. each child record can have n grandchild records. these records in published state. when user wants modify published document, need clone parent , of children , grandchildren.

the table structure looks 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 stored procedure, pass in quantlistid want clone @quantlistid. since quantlistattribute table has foreignkey can clone well.

insert [ql].[quantlist] (     [stateid],     [title], ) select      1,     title,     [ql].[quantlist]     quantlistid = @quantlistid  set @clonedid = scope_identity()  insert ql.quantlistattribute(         quantlistid         ,narrative)     select          @clonedid,         narrative,     ql.quantlistattribute     quantlistid = @quantlistid 

the trouble comes down attributereference. if cloned 30 quantlistattribute records, how clone records in reference table , match them new records inserted in quantlistattribute table?

    insert ql.attributereference(             quantlistattributeid,             reference,)         select              quantlistattributeid,             reference,         ql.quantlistreference         ??? don't have key go off of this. 

i thought temporary linking tables holds old attribute id's along new attribute id's. don't know how go inserting old attribute id's in temp table along new ones. inserting existing attributes, quantlistid, easy enough, can't figure out how make sure link correct new , old id's in way, attributereference table can cloned right. if quantlistattribute new , old id's linked, join on temp table , figure out how restore relationship of newly cloned references, newly cloned attributes.

any on awesome. i've spent last day , half trying figure out no luck :/

please excuse of sql inconsistencies. re-wrote sql real quick, trimming out lot of additional columns, related-tables , constraints weren't needed question.

edit

after doing little digging around, found output might useful this. there way use output map quantlistattributeid records inserted, quantlistattributeid originated from?

you can use output inserted rows.

1. you can insert data quantlistattribute based on order of order c.quantlistattributeid asc

2. have temp table/table variable 3 columns

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

3. use output insert new identity values of quantlistattribute temp table/table variable. new ids generated in same order c.quantlistattributeid

4. use row_number() ordered quantlistattributeid match old quantlistattributeid , new quantlistattributeids based on row_number() , id of table variable , update values or old quantlistattributeid in table variable

5. use temp table , join attributereference , insert records in 1 go.

note: order by during insert select , row_number() matching old quantlistattributeid required because looking @ question, there seems no other logical key map old , new records together.

query above steps

declare @clonedid int,@quantlistid int = 0  insert [ql].[quantlist] (     [stateid],     [title] ) select      1,     title     [ql].[quantlist]     quantlistid = @quantlistid  set @clonedid = scope_identity() --define table variable store new quantlistattributeid , use map old quantlistattributeid declare @temp table(id int identity(1,1), newattrid int,oldattrid int)  insert ql.quantlistattribute(         quantlistid         ,narrative)         --new quantlistattributeid created in same order old quantlistattributeid  because of order         output inserted.quantlistattributeid,null @temp     select          @clonedid,         narrative     ql.quantlistattribute c     quantlistid = @quantlistid     --this required keep new ids generated in same order old     order c.quantlistattributeid asc      ;with cte     (         select c.quantlistattributeid,         --use row_number matching id same 1 generated in @temp         row_number()over(order c.quantlistattributeid asc) id         ql.quantlistattribute c         quantlistid = @quantlistid     )     --update old value in @temp      update t     set oldattrid = cte.quantlistattributeid     @temp t     inner join cte on t.id = cte.id   insert ql.attributereference(             quantlistattributeid,             reference)         select              t.newattrid,             reference         ql.attributereference r         --use oldattrid join ql.attributereference , insert newattrid         inner join @temp t         on t.oldattrid = r.quantlistattributeid 

hope helps.