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;