Copy Multiple Records with a Master-Details Relationship

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     |
+-------------------+---------------+------+---------+

Select multiple records from sql database table in a master-detail scenario

Select ....
From Details As D
    Left Join   (
                Select M1.MasterId, M.DetailId, M.Date...
                From Master As M1
                Where MasterId  = (
                                    Select Max(M2.MasterId)
                                    From Master As M2
                                    Where M2.DetailId = M1.DetailId
                                    Group By M2.DetailId
                                    Having Max(M2.Date) = M1.Date
                                    )
                ) As M
        On M.DetailId = D.DetailId

What I'm doing here is dealing with ties. If you had two Master rows with the same DetailId and Date, I'm choosing the one with the highest MasterId.

Btw, this problem is significantly simpler if you have common table expressions (CTE). With a CTE you can do something like:

With LastMasterRows As
                (
                Select MasterId, DetailId, Date
                    , Row_Number() Over( Partition By DetailId Order By Date, MasterId ) As ItemRank
                From Master
                )
Select ...
From Details As D
    Left Join LastMasterRows As M
        On M.DetailId = D.DetailId
            And M.ItemRank = 1

How to copy records from inter-linked tables to another in a different database?

First Solution

Using MERGE and OUTPUT together

OUTPUT combined with MERGE function has the ability to retrieve the old primary keys before inserting into the table.

Second Solution

NOTE: This solution only works if you are sure that you have another column that has its values unique in the table besides the table's primary key.

You may use this column as a link between the table in the source database and its sister table in the target database. The code below is an example taking into account that First.Name has unique values when month > 6.

-- no changes to insert code in First table
INSERT INTO First
(Name, Year)
SELECT  Name, Year
FROM    DB_A.dbo.First
WHERE Year >= 1992

INSERT INTO Second
(FirstID, Day, Month)
SELECT  CurrentF.FirstID, Day, Month -- 2. Use the FirstID that has been input in First table
FROM    DB_A.dbo.Second S INNER JOIN
        DB_A.dbo.First F ON S.FirstID = F.FirstID INNER JOIN
        First CurrentF ON CurrentF.Name = F.Name -- 1. Join Name as a link
WHERE Month > 6

INSERT INTO Third
(SecondID, Speed, Remark)
SELECT  CurrentS.SecondID, Speed, Remark --5. Get the proper SecondID
FROM    DB_A.dbo.Third T INNER JOIN
        DB_A.dbo.Second S ON T.SecondID = S.SecondID INNER JOIN
        DB_A.dbo.First F ON F.FirstID = S.FirstID INNER JOIN
        First CurrentF ON CurrentF.Name = F.Name INNER JOIN -- 3. Join using Name as Link
        Second CurrentS ON CurrentS.FirstID= CurrentF.FirstID -- 4. Link Second and First table to get the proper SecondID.
WHERE Remark <> NULL

Copying table values with dependent child records

The usual way to insert a row and get the generated sequence value in a single statement is to use the RETURNING INTO clause. Unfortunately, this clause is not supported with the multi-row INSERT SELECT statement (as of 11.2.0.2.0).

So you will have to either:

• use a cursor and insert in the parent table row-by-row (then use one multi-row insert in the child table for each row in the parent table).

  For instance, in your case:

  DECLARE
     l_key aparent.aparent_code%TYPE;
  BEGIN
     FOR cc IN (SELECT a.aparent_code, a.value1, a.value2 
                  FROM aparent a 
                 WHERE a.avalue1 = 10) LOOP
        INSERT INTO aparent 
           VALUES (sq_aparent.nextval, cc.value1, cc.value2) 
           RETURNING aparent_code INTO l_key; -- get the new parent key
        INSERT INTO achild 
           (SELECT sq_achild.nextval, l_key, value3 -- use the new parent key
              FROM achild 
             WHERE aparent_code = cc.aparent_code);
     END LOOP;
  END;
  

• use multi-row inserts with a workaround as you did.

I think your approach may be the most efficient generally, especially if many parent rows are being copied. The overhead of inserting into the temporary table and joining to it should be minimal compared to the large overhead incurred by many single-row statements.

Best Practice for Master-Detail relationship when ALL combinations are required

I don't know if this would count as 'Best' practice, but I've certainly seen it a lot in the wild, and therefore is probably at least 'Good' practice.

The Event table should probably have a 'type' column - defined by you, could be numeric or text ('class' or 'school', 0 or 1, etc.). Then, there would only be entries in the event_class table for events of type 'class'.

When retrieving class-specific data for class_id, your join logic would look like:

SELECT * FROM event, event_class
WHERE event_class.class_id = this_class.id
    AND event.type = 'class'
    AND event.id = event_class.event_id

If you wanted all the class AND school event data for class_id, it would look like:

SELECT * FROM event, event_class
WHERE (event_class.class_id = this_class.id
    AND event.type = 'class'
    AND event.id = event_class.event_id)
OR event.type = 'school'

Mind the parentheses on the second one to make sure the boolean logic works correctly. None of this is tested I'm afraid - just an idea. There are probably ways of optimising the joins, but for 20 classes, it's likely not worth the effort.

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

---

Related Topics