在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

我想创建新的Model行和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列。我认为这需要递归完成。

有什么建议吗？

- navigator

---

你为什么不使用hierarchyid？ - Panagiotis Kanavos

@PanagiotisKanavos，这是一个SQL Server 2008数据库。 - navigator

不要紧，hierarchyid 已经在 2005 年被添加了。 - Panagiotis Kanavos

有趣。我会去看看。 - navigator

Answer 1

这里描述的解决方案可以在多用户环境中正常运行。您不需要锁定整个表。您不需要禁用自引用外键。您不需要递归。

使用 MERGE 和 OUTPUT 子句来 (滥用) 实现。 MERGE 可以插入 (INSERT)、更新 (UPDATE) 和删除 (DELETE) 行。在我们的情况下，我们只需要 INSERT。1 = 0 始终为 false，因此始终执行 NOT MATCHED BY TARGET 部分。一般来说，可能还有其他分支，请参阅文档。通常使用 WHEN MATCHED 进行更新；通常使用 WHEN NOT MATCHED BY SOURCE 进行删除，但我们在这里不需要它们。

这种复杂的MERGE形式相当于简单的INSERT，但与简单的INSERT不同的是，它的OUTPUT子句允许引用我们需要的列。它允许从源表和目标表中检索列，从而节省了旧ID和新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;

- Vladimir Baranov

---

这看起来很有前途。我明天会试一下并告诉你……我有大约500多个项目需要建模。 - navigator

通过 OUTPUT Src.ITEMID AS OldItemID, inserted.ITEMID AS NewItemID 来获取旧 ID 和新 ID - 这个技巧救了我的一天。+1并谢谢你。 - EvilDr

Answer 2

你可以不使用递归来实现。但是为了确保它能正常工作，你需要先锁定表。

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

这个想法是从ITEM表中选择原始模型的所有行，并为它们生成虚假ID。

虚假ID是：

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

接下来我们有一个映射：oldid -> newid

然后我们将原始模型插入到ITEM表中，就像它本来一样，但是我们用映射中的记录替换Parent。

我能看到的问题是，在插入行时，某些ItemID可能仍不存在于Parent中（即，我们插入将具有ItemID 20但其Parent为21的行）。为此，我们可能需要在执行此插入的时间内禁用Parent上的约束。之后我们应该再次启用它。数据当然是正确的。

- dcieslak

---

我在我的答案中添加了解释。这有点棘手，但非常高效。 - dcieslak

@dcieslak，如果您想保证在INSERT期间生成新的IDENTITY值的顺序，请指定ORDER BY。 - Vladimir Baranov

@VladimirBaranov 排序不会有帮助。请检查最后几个句子。你提到的问题有解决方案。 - dcieslak

@dcieslak，目前查询的问题在于您使用ROW_NUMBER按特定顺序(ORDER BY itemid)生成新的虚拟ID(currID)。但是，INSERT将在Items表中生成的实际IDENTITY值可以以任何顺序生成，除非您指定ORDER BY。 - Vladimir Baranov