Copying multiple records with reference to the main parts

Question

Copying multiple records with reference to the main parts

I ran into a problem in SQL Server while copying multiple related tables at the same time.

I have two tables. One of them is StageDetails, and the other is StageDetailsItem.

The table StageDetailscontains three rows, and the table StageDetailsItemcontains 15 rows. Each line of StageDetailshas five lines in StageDetailsItem.

Between StateDetailsand StageDetailsItemsthere is a relationship between the master data.

I want to copy three records StageDetailsand fifteen records StageDetailsItemin one shot into the same tables, and I want to change StageDetailIDof StageDetailsItem when StageDetailsIteminserted.

I do not want to use an explicit loop, for example CURSOR, WHILEetc.

Here is the DDL script for StageDetailsand 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

:

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

+2

sql sql-server sql-server-2012

Shohel 15 . '14 16:37

1

Vladimir Baranov · Accepted Answer · 2014-12-16T05:37:38+0000

, , .

MERGE OUTPUT.

MERGE INSERT, UPDATE DELETE . INSERT.

1=0 false, NOT MATCHED BY TARGET . , , . . WHEN MATCHED UPDATE; WHEN NOT MATCHED BY SOURCE DELETE, .

MERGE INSERT, INSERT OUTPUT . , , IDENTITY.

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 @T TABLE(OldStageDetailID int, NewStageDetailID int);

StageDetail, .

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

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

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

Copying multiple records with reference to the main parts

More articles: