复制具有主从关系的多条记录

本文介绍了复制具有主从关系的多条记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在 SQL Server 中遇到了一次复制多个相关表的问题.

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

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

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

StageDetails 表包含三行，StageDetailsItem 表包含十五行.StageDetails 中的每一行在 StageDetailsItem 中有五行.

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

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

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

我想一次性复制三个 StageDetails 记录和十五个 StageDetailsItem 记录到相同的表中，我想更改 StageDetailsItem 的 StageDetailID插入 StageDetailsItem 时.

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.

我不想使用显式循环，例如 CURSOR、WHILE 等

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

这是 StageDetails 和 StageDetailsItem 的 DDL 脚本.

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;

推荐答案

此处描述的解决方案将在多用户环境中正常工作.

The solution described here will work correctly in multi-user environment.

我会使用 MERGE使用 OUTPUT 子句.

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

MERGE can INSERT, UPDATE and DELETE rows. In this case we need only INSERT.

1=0 总是假的，所以 NOT MATCHED BY TARGET 部分总是被执行.通常，可能还有其他分支，请参阅文档.WHEN MATCHED 通常用于UPDATE；WHEN NOT MATCHED BY SOURCE 通常用于DELETE，但我们这里不需要它们.

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.

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

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.

示例数据

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

查询

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

Declare a table variable (or temp table) to hold the mapping between old and new IDs.

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

首先从 StageDetail 表中复制行，记住表变量中 ID 的映射.

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

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

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 ;

结果

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