sql - Copy multiple records with a master-details relationship

Question

I am facing a problem in SQL Server with copying multiple related tables at a time.

I have two tables. One is StageDetails and another is StageDetailsItem.

The StageDetails table contains three rows and the StageDetailsItem table contains fifteen rows. Each row from StageDetails has five rows in StageDetailsItem.

There is a master-details relationship between StateDetails and StageDetailsItems.

I want to copy three StageDetails records and fifteen StageDetailsItem records in one shot into the same tables and I want to change StageDetailID of StageDetailsItem when StageDetailsItem is being inserted.

I don't want to use an explicit loop, like CURSOR, WHILE, etc.

Here is the DDL script for StageDetails and StageDetailsItem.

CREATE TABLE [dbo].[StageDetail](
    [StageDetailID] [int] IDENTITY(1,1) NOT NULL,
    [StageNUmber] [nvarchar](50) NULL,
    [TypeOfStage] [nvarchar](500) NULL,
    [Distance] [nvarchar](500) NULL,
CONSTRAINT [PK_StageDetail] PRIMARY KEY CLUSTERED 
(
    [StageDetailID] ASC
) WITH (
    PAD_INDEX = OFF, 
    STATISTICS_NORECOMPUTE = OFF, 
    IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StageDetailItem](
    [StageDetailItemID] [int] IDENTITY(1,1) NOT NULL,
    [StageDetailID] [int] NULL,
    [Road] [nvarchar](500) NULL,
    [CostPer] [nvarchar](500) NULL,
CONSTRAINT [PK_StageDetailItem] PRIMARY KEY CLUSTERED 
(
    [StageDetailItemID] ASC
) WITH (
    PAD_INDEX = OFF, 
    STATISTICS_NORECOMPUTE = OFF, 
    IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[StageDetailItem] WITH CHECK 
ADD CONSTRAINT [FK_StageDetailItem_StageDetail] FOREIGN KEY([StageDetailID])
REFERENCES [dbo].[StageDetail] ([StageDetailID])
GO

ALTER TABLE [dbo].[StageDetailItem] 
CHECK CONSTRAINT [FK_StageDetailItem_StageDetail]
GO

I can easily copy records from one table like this:

INSERT INTO EventDetailsEventType(EventID, EventTypeID)
SELECT @EventDetailsID, EventTypeID 
FROM EventDetailsEventType 
WHERE EventID = @ParentEventID;

Answer 1

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