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;

What have you tried so far? Is it just the whole contents of these tables to be copied? — Paddy, Dec 15 '14 at 16:40
Are you copying it to another table if so what is the destination table structure — Pரதீப், Dec 15 '14 at 16:44
@NoDisplayName, source and destination table are same but they will maintain their repationship. I want to reinsert. — Shohel, Dec 15 '14 at 16:47
@Paddy, Hey I have no idea about this that's why i did not try this. but i have done this work into one table. can i give you that code? — Shohel, Dec 15 '14 at 16:49
what is the reason behind doing this why do you want to duplicate records. — Pரதீப், Dec 15 '14 at 16:53
@NoDisplayName, The user will copy the whole Stage, after copying this he will change little bit. Actually not duplicate records. StageDetailsID and StageDetailsItemID will be change because those will be identity. — Shohel, Dec 15 '14 at 16:57

Vladimir Baranov · Accepted Answer · 2016-07-07T07:54:43.263

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

Thanks your reply. after checking it i will knock you. – Shohel Dec 16 '14 at 09:17 — Shohel, Dec 16 '14 at 09:17

Copy multiple records with a master-details relationship

1 Answers1

Linked