sql - Cascade copy a row with all child rows and their child rows, etc

Question

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

• A project can have multiple blocks (a block being a single standing building)
• A block can have multiple elevations (building face; north, east, etc)
• An elevation can have many floors (a tall building)
• A floor can have many panels (a panel being a section of concrete cladding)

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)

As far as tables go -

Blocks

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

Elevations

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

Floors

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

Panels

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

If I copy a block it needs to

1. Copy the current block, but the user should be able to choose Blocks.name.
2. Copy all of the elevations on that block
3. Copy all floors on each of those elevations
4. Copy all panels on each of those floors

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)

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.

Answer 1

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

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

I'd use MERGE with OUTPUT clause.

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

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.

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

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

Elevations

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

Floors

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

Panels

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
;