级联复制一行,其中包含所有子行及其子行等 [英] 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
- 复制当前块,但用户应该能够选择 Blocks.name.
- 复制该块上的所有高程
- 复制每个立面的所有楼层
- 复制每个楼层的所有面板
如果您还需要了解任何其他信息,请告诉我,提前感谢您的帮助!
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 manyElevations
. - One
Elevation
has manyFloors
. - One
Floor
has manyPanels
.
我会使用 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屋!