Make a copy of parent-child structure in SQL

Question

I have a table MODELS to which several ITEMS can belong. The ITEMS table is a hierarchical table with a self join on the PARENT column. Root level items will have Null in PARENT. Items can go to any level deep.

create table MODELS (
   MODELID              int                  identity,
   MODELNAME            nvarchar(200)         not null,
   constraint PK_MODELS primary key (MODELID)
)
go

create table ITEMS (
   ITEMID               int                  identity,
   MODELID              int                  not null,
   PARENT               int                  null,
   ITEMNUM              nvarchar(20)         not null,
   constraint PK_ITEMS primary key (ITEMID)
)
go

alter table ITEMS
   add constraint FK_ITEMS_MODEL foreign key (MODELID)
      references MODELS (MODELID)
go

alter table ITEMS
   add constraint FK_ITEMS_ITEMS foreign key (PARENT)
      references ITEMS (ITEMID)
go

I wish to create stored procedure to copy a row in the MODELS table into a new row and also copy the entire structure in ITEMS as well.

For example, if I have the following in ITEMS:

ITEMID    MODELID    PARENT    ITEMNUM
  1          1        Null       A
  2          1        Null       B
  3          1        Null       C
  4          1          1        A.A
  5          1          2        B.B
  6          1          4        A.A.A
  7          1          4        A.A.B
  8          1          3        C.A
  9          1          3        C.B
 10          1          9        C.B.A

I'd like to create new Model row and copies of the 10 Items that should be as follows:

ITEMID    MODELID    PARENT    ITEMNUM
  11          2       Null       A
  12          2       Null       B
  13          2       Null       C
  14          2        11        A.A
  15          2        12        B.B
  16          2        14        A.A.A
  17          2        14        A.A.B
  18          2        13        C.A
  19          2        13        C.B
  20          2        19        C.B.A

I will pass the MODELID to be copied as a parameter to the Stored Procedure. The tricky part is setting the PARENT column correctly. I think this will need to be done recursively.

Any suggestions?

Answer 1

The solution described here will work correctly in multi-user environment. You don't need to lock the whole table. You don't need to disable self-referencing foreign key. You don't need recursion.

(ab)use MERGE with OUTPUT clause.

MERGE can INSERT, UPDATE and DELETE rows. In our case we need only to INSERT. 1=0 is always false, so the NOT MATCHED BY TARGET part is always executed. In general, there could be other branches, see docs. WHEN MATCHED is usually used to UPDATE; WHEN NOT MATCHED BY SOURCE is usually used to DELETE, but we don't need them here.

This convoluted form of MERGE is equivalent to simple INSERT, but unlike simple INSERT its OUTPUT clause allows to refer to the columns that we need. It allows to retrieve columns from both source and destination tables thus saving a mapping between old and new IDs.

sample data

DECLARE @Items TABLE (
   ITEMID               int                  identity,
   MODELID              int                  not null,
   PARENT               int                  null,
   ITEMNUM              nvarchar(20)         not null
)

INSERT INTO @Items (MODELID, PARENT, ITEMNUM) VALUES
(1, Null, 'A'),
(1, Null, 'B'),
(1, Null, 'C'),
(1,   1 , 'A.A'),
(1,   2 , 'B.B'),
(1,   4 , 'A.A.A'),
(1,   4 , 'A.A.B'),
(1,   3 , 'C.A'),
(1,   3 , 'C.B'),
(1,   9 , 'C.B.A');

I omit the code that duplicates the Model row. Eventually you'll have ID of original Model and new Model.

DECLARE @SrcModelID int = 1;
DECLARE @DstModelID int = 2;

Declare a table variable (or temp table) to hold the mapping between old and new item IDs.

DECLARE @T TABLE(OldItemID int, NewItemID int);

Make a copy of Items remembering the mapping of IDs in the table variable and keeping old PARENT values.

MERGE INTO @Items
USING
(
    SELECT ITEMID, PARENT, ITEMNUM
    FROM @Items AS I
    WHERE MODELID = @SrcModelID
) AS Src
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT (MODELID, PARENT, ITEMNUM)
VALUES
    (@DstModelID
    ,Src.PARENT
    ,Src.ITEMNUM)
OUTPUT Src.ITEMID AS OldItemID, inserted.ITEMID AS NewItemID
INTO @T(OldItemID, NewItemID)
;

Update old PARENT values with new IDs

WITH
CTE
AS
(
    SELECT I.ITEMID, I.PARENT, T.NewItemID
    FROM
        @Items AS I
        INNER JOIN @T AS T ON T.OldItemID = I.PARENT
    WHERE I.MODELID = @DstModelID
)
UPDATE CTE
SET PARENT = NewItemID
;

Check the results

SELECT * FROM @Items;

Answer 2

You can do it without recursion. But you need to lock the table first maybe to be sure that it works fine.

insert into items (Modelid, Parent, ITEMNUM)
 select  2 as modelId, 
          MAP.currId as Parent,
          MO.ITEMNUM  
  from (
        ( select * from items where MODELID = 1) MO
  left join 
        ( select IDENT_CURRENT('ITEMS') + ROW_NUMBER() OVER(ORDER BY itemid ) currID , 
                 i.ItemID 
          from ITEMS i
         where modelid = 1 ) MAP
  on MO.Parent= MAP.ItemID 
   )  ORDER BY MO.ItemID

The idea behind it is that we select all rows from original model in ITEM table and we generate fake ID for them.

The fake ID is :

Row 1 = current identity + 1,
Row 2 = current identity + 2, 
etc.

After that we have mapping : oldid -> newid

Then we insert original model to ITEM table like it is but we replace Parent by record from our mapping.

The issue that I can see is that some ItemID may still not exist for Parent when we insert rows (ie. we insert row that will have ItemID 20 but its Parent is 21). For that we may need to disable constraint on Parent for the time of execution of this insert. After that we supposed to enable it again. Data will be correct of course.