级联复制包含所有子行及其子行等的行

Question

我正在尝试跟踪商业建筑的项目（特别是混凝土覆层）。

• 一个项目可以有多个街区（一个街区是一个独立的建筑物）
• 一个街区可以有多个高程（建筑面；北，东等）
• 海拔高度可以有多个楼层（高层建筑）
• 地板可以有许多面板（面板是混凝土外墙的一部分）

为了使在数据库中构建面板时可以更轻松地进行跟踪，我希望能够复制一个块（由于10个块中有9个，因此每个块都是相同的，只是做了较小的更改）

就桌子而言-

积木

Blocks.BlockID, 
Blocks.BlockName, 
Blocks.BlockDescription, 
Projects.ProjectID

高程

Elevations.ElevationID, 
Elevations.ElevationName, 
Elevations.ElevationDescription, 
Blocks.BlockID

楼层数

Floors.FloorID, 
Floors.FloorName, 
Floors.FloorDescription, 
Elevations.ElevationID

面板

Panels.PanelID, 
Panels.PanelName, 
Panels.PanelDescription, 
Floors.FloorID

如果我复制一个块，则需要

1. 复制当前块，但用户应该能够选择Blocks.name。
2. 复制该街区上的所有高程
3. 复制每个海拔上的所有楼层
4. 复制每个楼层的所有面板

让我知道您是否还需要其他任何信息，谢谢您的提前帮助！

我的尝试（经过先前的建议）

DECLARE @blockToCopy int = 1
DECLARE @BlockOutput table
( BlockID int);
DECLARE @ElevationsOutput table
(  ElevationID int, BlockID int );
DECLARE @FloorsOutput table
(  FloorID int, ElevationID int );
DECLARE @ItemsOutput table
(  ItemID int, FloorID int );

INSERT INTO Blocks
(ProjectID,BlockName,BlockDescription) 
    OUTPUT 
    INSERTED.BlockID
    INTO @BlockOutput
SELECT ProjectID,'NewNameTest','NewDescTest' 
from Blocks
WHERE BlockID=@blockToCopy

INSERT INTO Elevations
(BlockID,ElevationName,ElevationDescription) 
    OUTPUT 
    INSERTED.ElevationID,
    INSERTED.BlockID
    INTO @ElevationsOutput
SELECT (SELECT BlockID from @BlockOutput),ElevationName,ElevationDescription 
from Elevations
WHERE BlockID=@blockToCopy

INSERT INTO Floors
(ElevationID,FloorName,FloorDescription)
    OUTPUT 
    INSERTED.FloorID,
    INSERTED.ElevationID
    INTO @FloorsOutput
SELECT (SELECT top 1 ElevationID from @ElevationsOutput order by ElevationID desc), FloorName,FloorDescription 
from Floors
WHERE ElevationID in (SELECT ElevationID from @ElevationsOutput)

在第二个级联（尝试复制每个高程内的所有楼层）之后，它出错了。我知道为什么会出错（不是将原始ID与要复制到的新ID一起列出，而是由他们自己输出新ID，因此它没有要复制的内容，因为它们尚不存在），但我不知道如何解决。

Answer 1

我认为Blocks.BlockID，Elevations.ElevationID，Floors.FloorID，Panels.PanelID是主键和自动生成的IDENTITY。

• 一个Block有很多Elevations。
• 一个Elevation有很多Floors。
• 一个Floor有很多Panels。

我会用MERGEwithOUTPUT子句。

MERGE可以INSERT，UPDATE和DELETE行。在这种情况下，我们只需要INSERT。

1=0始终为假，因此NOT MATCHED BY TARGET始终执行该部分。通常，可能还有其他分支，请参阅文档。 WHEN MATCHED通常用于UPDATE; WHEN NOT MATCHED BY SOURCE通常用于DELETE，但是我们在这里不需要它们。

这种复杂的形式MERGE等效于simple
INSERT，但与simple不同，INSERT它的OUTPUT子句允许引用我们需要的列。它允许从源表和目标表中检索列，从而节省了旧的现有ID和由生成的新ID之间的映射IDENTITY。

堵塞

复制给定的一个，Block并记住ID新的Block。我们可以在此处使用simpleINSERT和SCOPE_IDENTITY，因为它BlockID是主键，并且只能插入一行。

DECLARE @blockToCopy int = 1;
DECLARE @VarNewBlockID int;
INSERT INTO Blocks
    (ProjectID
    ,BlockName
    ,BlockDescription)
SELECT
    ProjectID
    ,'NewNameTest'
    ,'NewDescTest'
FROM Blocks
WHERE Blocks.BlockID = @blockToCopy
;
SET @VarNewBlockID = SCOPE_IDENTITY();

高程

Elevations从旧版本复制Block并分配给新版本Block。还记得老之间的映射IDs和新生成的IDs在@MapElevations。

DECLARE @MapElevations TABLE(OldElevationID int, NewElevationID int);

MERGE INTO Elevations
USING
(
    SELECT
        ElevationID
        ,@VarNewBlockID AS BlockID
        ,ElevationName
        ,ElevationDescription
    FROM Elevations
    WHERE Elevations.BlockID = @blockToCopy
) AS Src
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT
    (BlockID
    ,ElevationName
    ,ElevationDescription)
VALUES
    (Src.BlockID
    ,Src.ElevationName
    ,Src.ElevationDescription)
OUTPUT
    Src.ElevationID AS OldElevationID
    ,inserted.ElevationID AS NewElevationID
INTO @MapElevations(OldElevationID, NewElevationID)
;

楼层数

Floors使用新旧版本之间的映射进行复制ElevationID。还记得老之间的映射IDs和新生成的IDs在@MapFloors。

DECLARE @MapFloors TABLE(OldFloorID int, NewFloorID int);

MERGE INTO Floors
USING
(
    SELECT
        Floors.FloorID
        ,M.NewElevationID AS ElevationID
        ,Floors.FloorName
        ,Floors.FloorDescription
    FROM
        Floors
        INNER JOIN Elevations ON Elevations.ElevationID = Floors.ElevationID
        INNER JOIN @MapElevations AS M ON M.OldElevationID = Elevations.ElevationID
    WHERE Elevations.BlockID = @blockToCopy
) AS Src
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT
    (ElevationID
    ,FloorName
    ,FloorDescription)
VALUES
    (Src.ElevationID
    ,Src.FloorName
    ,Src.FloorDescription)
OUTPUT
    Src.FloorID AS OldFloorID
    ,inserted.FloorID AS NewFloorID
INTO @MapFloors(OldFloorID, NewFloorID)
;

面板

Panels使用新旧版本之间的映射进行复制FloorID。这是最后一级的细节，因此我们可以使用简单的方法，INSERT而无需记住的映射IDs。

INSERT INTO Panels
    (FloorID
    ,PanelName
    ,PanelDescription)
SELECT
    M.NewFloorID
    ,Panels.PanelName
    ,Panels.PanelDescription
FROM
    Panels
    INNER JOIN Floors ON Floors.FloorID = Panels.FloorID
    INNER JOIN Elevations ON Elevations.ElevationID = Floors.ElevationID
    INNER JOIN @MapFloors AS M ON M.OldFloorID = Floors.FloorID
WHERE Elevations.BlockID = @blockToCopy
;