我在sql Server中遇到一个问题,一次复制多个相关表.
我有两张桌子.一个是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 | +-------------------+---------------+------+---------+
