我认为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
;