Cascade copy a row with all child rows and their child rows, etc
I assume that Blocks.BlockID
, Elevations.ElevationID
, Floors.FloorID
, Panels.PanelID
are primary keys and autogenerated IDENTITY
.
- One
Block
has manyElevations
. - One
Elevation
has manyFloors
. - One
Floor
has manyPanels
.
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
;
Copy multiple records with a master-details relationship
The solution described here will work correctly in multi-user environment.
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
.
Sample data
INSERT INTO [dbo].[StageDetail]
([StageNUmber]
,[TypeOfStage]
,[Distance])
VALUES
('sn01','t1','D1'),
('sn02','t2','D2'),
('sn03','t3','D3');
INSERT INTO [dbo].[StageDetailItem]
([StageDetailID]
,[Road]
,[CostPer])
VALUES
(1,'r1_1','C11'),
(1,'r1_2','C12'),
(1,'r1_3','C13'),
(1,'r1_4','C14'),
(1,'r1_5','C15'),
(2,'r2_1','C16'),
(2,'r2_2','C17'),
(2,'r2_3','C18'),
(2,'r2_4','C19'),
(2,'r2_5','C20'),
(3,'r3_1','C21'),
(3,'r3_2','C22'),
(3,'r3_3','C23'),
(3,'r3_4','C24'),
(3,'r3_5','C25');
Query
Declare a table variable (or temp table) to hold the mapping between old and new IDs.
DECLARE @T TABLE(OldStageDetailID int, NewStageDetailID int);
At first make a copy of rows from StageDetail
table remembering the mapping of IDs in the table variable.
MERGE INTO [dbo].[StageDetail]
USING
(
SELECT [StageDetailID],[StageNUmber],[TypeOfStage],[Distance]
FROM [dbo].[StageDetail]
) AS Src
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT ([StageNUmber],[TypeOfStage],[Distance])
VALUES
(Src.[StageNUmber]
,Src.[TypeOfStage]
,Src.[Distance])
OUTPUT
Src.[StageDetailID] AS OldStageDetailID
,inserted.[StageDetailID] AS NewStageDetailID
INTO @T(OldStageDetailID, NewStageDetailID)
;
Then copy rows from StageDetailItem
using new StageDetailIDs
.
INSERT INTO [dbo].[StageDetailItem]
([StageDetailID]
,[Road]
,[CostPer])
SELECT
T.[NewStageDetailID]
,[dbo].[StageDetailItem].[Road]
,[dbo].[StageDetailItem].[CostPer]
FROM
[dbo].[StageDetailItem]
INNER JOIN @T AS T ON T.OldStageDetailID = [dbo].[StageDetailItem].StageDetailID
;
Result
SELECT * FROM [dbo].[StageDetail]
+---------------+-------------+-------------+----------+
| StageDetailID | StageNUmber | TypeOfStage | Distance |
+---------------+-------------+-------------+----------+
| 1 | sn01 | t1 | D1 |
| 2 | sn02 | t2 | D2 |
| 3 | sn03 | t3 | D3 |
| 4 | sn01 | t1 | D1 |
| 5 | sn02 | t2 | D2 |
| 6 | sn03 | t3 | D3 |
+---------------+-------------+-------------+----------+
.
SELECT * FROM [dbo].[StageDetailItem]
+-------------------+---------------+------+---------+
| StageDetailItemID | StageDetailID | Road | CostPer |
+-------------------+---------------+------+---------+
| 1 | 1 | r1_1 | C11 |
| 2 | 1 | r1_2 | C12 |
| 3 | 1 | r1_3 | C13 |
| 4 | 1 | r1_4 | C14 |
| 5 | 1 | r1_5 | C15 |
| 6 | 2 | r2_1 | C16 |
| 7 | 2 | r2_2 | C17 |
| 8 | 2 | r2_3 | C18 |
| 9 | 2 | r2_4 | C19 |
| 10 | 2 | r2_5 | C20 |
| 11 | 3 | r3_1 | C21 |
| 12 | 3 | r3_2 | C22 |
| 13 | 3 | r3_3 | C23 |
| 14 | 3 | r3_4 | C24 |
| 15 | 3 | r3_5 | C25 |
| 16 | 4 | r1_1 | C11 |
| 17 | 4 | r1_2 | C12 |
| 18 | 4 | r1_3 | C13 |
| 19 | 4 | r1_4 | C14 |
| 20 | 4 | r1_5 | C15 |
| 21 | 5 | r2_1 | C16 |
| 22 | 5 | r2_2 | C17 |
| 23 | 5 | r2_3 | C18 |
| 24 | 5 | r2_4 | C19 |
| 25 | 5 | r2_5 | C20 |
| 26 | 6 | r3_1 | C21 |
| 27 | 6 | r3_2 | C22 |
| 28 | 6 | r3_3 | C23 |
| 29 | 6 | r3_4 | C24 |
| 30 | 6 | r3_5 | C25 |
+-------------------+---------------+------+---------+
How to retrieve all recursive children of parent row in Oracle SQL?
@AlexPoole answer is great, I just want to extend his answer with more intuitive variant of query for summing values along a path.
This variant based on recursive subquery factoring feature, introduced in Oracle 11g R2
.
with recursion_view(base, parent_id, child_id, qty) as (
-- first step, get rows to start with
select
parent_id base,
parent_id,
child_id,
qty
from
md_boms
union all
-- subsequent steps
select
-- retain base value from previous level
previous_level.base,
-- get information from current level
current_level.parent_id,
current_level.child_id,
-- accumulate sum
(previous_level.qty + current_level.qty) as qty
from
recursion_view previous_level,
md_boms current_level
where
current_level.parent_id = previous_level.child_id
)
select
base, parent_id, child_id, qty
from
recursion_view
order by
base, parent_id, child_id
SQLFiddle example (extended with one data row to demonstrate work with more then 2 levels)
Related Topics
Fetch Table Name from a Column for from Clause
Format Function Not Working in SQL Server 2008 R2
Select Columns from One Table Based on the Column Names from Another Table
Rename a Column in All the Tables - SQL
Get "Zero" for a Count at Dates Without Records
How to Get Rows Having Sum Equal to Given Value
Querying and Selecting Specific Column in SQLalchemy
SQL - Converting 24-Hour ("Military") Time (2145) to "Am/Pm Time" (9:45 Pm)
How to Display Column Headers Returned by Postgresql Function
Handling Non Existent Values in SQL Query Expression for Ssrs Chart
Prevent Error When Dropping Not Existing Sequences, Creating Existing Users
How to Have Alphanumeric Sequence Generator in SQL
Elasticsearch Map Two SQL Tables with a Foreign Key
Get the Latest Records Per Group by SQL