sql - 在 SQL 中复制父子结构

Question

我有一个表MODELS，其中有多个ITEMS可以属于。 ITEMS 表是一个分层表，在 PARENT 列上具有自联接。根级项目在 PARENT 中将具有 Null。项目可以达到任何深度。

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

我希望创建存储过程来将 MODELS 表中的一行复制到新行中，并复制 ITEMS 中的整个结构。

例如，如果我在 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

我想创建新的模型行和 10 个项目的副本，应如下所示:

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

我会将要复制的MODELID 作为参数传递给存储过程。棘手的部分是正确设置 PARENT 列。我认为这需要递归地完成。

有什么建议吗？

Answer 1

此处描述的解决方案将在多用户环境中正常工作。您不需要锁定整个表。您不需要禁用自引用外键。您不需要递归。

(ab)使用MERGE与OUTPUT子句。

MERGE 可以INSERT、UPDATE 和DELETE 行。在我们的例子中，我们只需要INSERT。 1=0 始终为 false，因此始终执行 NOT MATCHED BY TARGET 部分。一般来说，可能还有其他分支，请参阅文档。 WHEN MATCHED 通常用于UPDATE； WHEN NOT MATCHED BY SOURCE 通常用于DELETE，但我们在这里不需要它们。

这种复杂形式的 MERGE 相当于简单的 INSERT，但与简单的 INSERT 不同，它的 OUTPUT 子句允许来引用我们需要的列。它允许从源表和目标表中检索列，从而保存新旧 ID 之间的映射。

示例数据

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');

我省略了与 Model 行重复的代码。最终您将获得原始模型和新模型的 ID。

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

声明一个表变量(或临时表)来保存新旧项目 ID 之间的映射。

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

制作Items的副本，记住表变量中ID的映射并保留旧的PARENT值。

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)
;

使用新 ID 更新旧的 PARENT 值

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
;

检查结果

SELECT * FROM @Items;