级联复制一行，其中包含所有子行及其子行等 [英] Cascade copy a row with all child rows and their child rows, etc

问题描述

我正在尝试跟踪商业建筑施工中的项目(特别是混凝土覆层).

I'm attempting to track items in commercial building construction (specifically concrete cladding).

• 一个项目可以有多个街区(一个街区是一个独立的建筑物)
• 一个街区可以有多个高度(建筑面；北面、东面等)
• 一个立面可以有很多层(高层建筑)
• 一个地板可以有许多面板(面板是混凝土覆层的一部分)

为了在数据库中构建面板时更容易进行跟踪，我希望能够复制一个块(因为 10 次中有 9 次，每个块都是相同的，但有细微的变化)

To make things easier when building the panels in the database so they can be tracked, I want to be able copy a block (since 9 times out of 10, each block is the same with minor changes)

就桌子而言 -

块

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

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

If I copy a block it needs to

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

如果您还需要了解任何其他信息，请告诉我，提前感谢您的帮助！

Let me know if you need to know anything else, thanks for any help in advance!

我的尝试(根据之前的建议)

My Attempt (after previous advice)

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，因此没有什么可复制的，因为它们尚不存在)但我不知道如何修复它.

It goes wrong after the second cascade (where trying to copy all floors within each elevation). I know why it's going wrong (instead of listing the original IDs alongside the new IDs to copy to, I'm outputting the new IDs by themselves so it has nothing to copy because they don't exist yet) but I don't know how to fix it.

推荐答案

我假设 Blocks.BlockID, Elevations.ElevationID, Floors.FloorID、Panels.PanelID 是主键和自动生成的 IDENTITY.

I assume that Blocks.BlockID, Elevations.ElevationID, Floors.FloorID, Panels.PanelID are primary keys and autogenerated IDENTITY.

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

• One Block has many Elevations.
• One Elevation has many Floors.
• One Floor has many Panels.

我会使用 MERGE 带有 OUTPUT 子句.

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

MERGE can INSERT, UPDATE and DELETE rows. In this case we need only INSERT.

1=0 总是假的，所以 NOT MATCHED BY TARGET 部分总是被执行.通常，可能还有其他分支，请参阅文档.WHEN MATCHED 通常用于UPDATE；WHEN NOT MATCHED BY SOURCE 通常用于DELETE，但我们这里不需要它们.

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.

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

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 existing IDs and new IDs generated by IDENTITY.

阻止

复制一个给定的Block并记住新Block的ID.我们可以在这里使用简单的 INSERT 和 SCOPE_IDENTITY，因为BlockID是主键，只能插入一行.

Copy one given Block and remember the ID of the new Block. We can use simple INSERT and SCOPE_IDENTITY here, because BlockID is primary key and only one row can be inserted.

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

海拔

从旧的 Block 复制 Elevations 并将它们分配给新的 Block.记住 @MapElevations 中旧 IDs 和新生成的 IDs 之间的映射.

Copy Elevations from old Block and assign them to the new Block. Remember the mapping between old IDs and freshly generated IDs in @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)
;

楼层

使用新旧ElevationID 之间的映射复制Floors.记住 @MapFloors 中旧 IDs 和新生成的 IDs 之间的映射.

Copy Floors using mapping between old and new ElevationID. Remember the mapping between old IDs and freshly generated IDs in @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)
;

面板

使用新旧FloorID 之间的映射复制Panels.这是最后一层细节，所以我们可以使用简单的 INSERT 而不要记住 IDs 的映射.

Copy Panels using mapping between old and new FloorID. This is the last level of details, so we can use simple INSERT and don't remember the mapping of 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
;

这篇关于级联复制一行，其中包含所有子行及其子行等的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！