Shohel Shohel - 5 months ago 18
SQL Question

Copy multiple records with a master-details relationship

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

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