sql – 使用主 – 详细信息关系复制多个记录

我有两张桌子.一个是StageDetails,另一个是StageDetailsItem.

StageDetails表包含三行,StageDetailsItem表包含十五行.
StageDetailsItem在StageDetailsItem中有五行.

StateDetails和StageDetailsItems之间存在主从关系.

我想一次复制三个StageDetails记录和十五个StageDetailsItem记录
进入相同的表,我想更改StageDetailsItem的StageDetailID
何时插入StageDetailsItem.

我不想使用显式循环,如CURSOR,WHILE等.

这是StageDetails和StageDetailsItem的DDL脚本.

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,[StageDetailID] [int] NULL,[Road] [nvarchar](500) NULL,[CostPer] [nvarchar](500) NULL,CONSTRAINT [PK_StageDetailItem] PRIMARY KEY CLUSTERED 
(
    [StageDetailItemID] ASC
) WITH (
    PAD_INDEX = OFF,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;

解决方法

我将MERGE与OUTPUT子句一起使用.

MERGE可以INSERT,UPDATE和DELETE行.
在这种情况下,我们只需要INSERT.

1 = 0始终为false,因此始终执行NOT MATCHED BY TARGET部分.
一般来说,可能还有其他分支,请参阅docs.
MATCHED通常用于更新;
什么时候不匹配来源通常用于删除,但我们在这里不需要它们.

这种错综复杂的MERGE形式相当于简单的INSERT,
但与简单的INSERT不同,它的OUTPUT子句允许引用我们需要的列.
它允许从源表和目标表中检索列,从而保存映射
旧现有ID与IDENTITY生成的新ID之间.

样本数据

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'),'r1_3','C13'),'r1_4','C14'),'r1_5','C15'),(2,'r2_1','C16'),'r2_2','C17'),'r2_3','C18'),'r2_4','C19'),'r2_5','C20'),(3,'r3_1','C21'),'r3_2','C22'),'r3_3','C23'),'r3_4','C24'),'r3_5','C25');

询问

声明一个表变量(或临时表)来保存旧ID和新ID之间的映射.

DECLARE @T TABLE(OldStageDetailID int,NewStageDetailID int);

首先从StageDetail表中创建行的副本,记住表变量中ID的映射.

MERGE INTO [dbo].[StageDetail]
USING
(
    SELECT [StageDetailID],[StageNUmber],[Distance]
    FROM [dbo].[StageDetail]
) AS Src
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT ([StageNUmber],[Distance])
VALUES
    (Src.[StageNUmber],Src.[TypeOfStage],Src.[Distance])
OUTPUT 
    Src.[StageDetailID] AS OldStageDetailID,inserted.[StageDetailID] AS NewStageDetailID
INTO @T(OldStageDetailID,NewStageDetailID)
;

然后使用新的StageDetailID从StageDetailItem复制行.

INSERT INTO [dbo].[StageDetailItem]
    ([StageDetailID],[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     |
+-------------------+---------------+------+---------+

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!