问题描述
我在 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 shotinto the same tables and I want to change StageDetailID of StageDetailsItemwhen 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 mappingbetween 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 |
+-------------------+---------------+------+---------+
这篇关于复制具有主从关系的多条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！