我正在嘗試復制現有船，其子表的所有內容（因為沒有更好的術語）及其關系。到目前為止，我已經完成了大部分的復制作業，除了船上的表格具有多對多的關系。我目前情況的一個例子如下：
我有兩個單獨的表和連接它們的第三個表：
設施
| FacilitiesId | ShipId | fName |
| ------------ | ------ | ---------- |
| 1 | 1 | Facility 1 |
| 2 | 1 | Facility 2 |
| 3 | 1 | Facility 3 |
甲板
| DeckId | ShipId | DeckLevel|
| ------ | ------ | -------- |
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
設施到甲板
| FacilitiesToDeckId | ShipId | FacilitiesId | DeckId |
| ------------------ | ------ | ------------ | ------ |
| 1 | 1 | 1 | 1 |
| 2 | 1 | 2 | 1 |
| 3 | 1 | 3 | 2 |
| 4 | 1 | 4 | 2 |
| 5 | 1 | 5 | 3 |
| 6 | 1 | 5 | 4 |
正如您在上面看到的，可以FacilitiesToDeck有多個相同的行，DeckId因為在同一層甲板上可以有多個設施。這同樣適用FacilitiesId，因為可以有跨越多個甲板層的設施。
現在，當我復制帶有子物體的船時，我希望上面的表格看起來像這樣：
設施
| FacilitiesId | ShipId | fName |
| ------------ | ------ | ---------- |
| 1 | 1 | Facility 1 |
| 2 | 1 | Facility 2 |
| 3 | 1 | Facility 3 |
| 4 | 2 | Facility 1 |
| 5 | 2 | Facility 2 |
| 6 | 2 | Facility 3 |
甲板
| DeckId | ShipId | DeckLevel|
| ------ | ------ | -------- |
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 2 | 1 |
| 5 | 2 | 2 |
| 6 | 2 | 3 |
設施到甲板
| FacilitiesToDeckId | ShipId | FacilitiesId | DeckId |
| ------------------ | ------ | ------------ | ------ |
| 1 | 1 | 1 | 1 |
| 2 | 1 | 2 | 1 |
| 3 | 1 | 3 | 2 |
| 4 | 1 | 4 | 2 |
| 5 | 1 | 5 | 3 |
| 6 | 1 | 5 | 4 |
| 7 | 2 | 6 | 5 |
| 8 | 2 | 7 | 5 |
| 9 | 2 | 8 | 6 |
| 10 | 2 | 9 | 6 |
| 11 | 2 | 10 | 7 |
| 12 | 2 | 10 | 8 |
我的存盤程序旨在從 中插入多行新資料FacilitiesToDeck，FacilitiesToDeck但其中ShipId和是我之前在存盤程序中為和FacilitiesId創建的新 ID 值。但是，我沒有看到（或在互聯網上找到）如何執行此操作的解決方案，因為插入的每個新行的每個值都是不同的，似乎沒有相關性（至少對我和我的同齡人而言）。DeckIdFacilitiesDeckFacilitiesToDeck
以下是我當前存盤程序的片段：
ALTER PROCEDURE [dbo].[CopyShip]
(@ShipId int,
@ShipName nvarchar(150),
@ShipCode nvarchar(8))
AS
BEGIN
SET NOCOUNT ON
DECLARE @CruiselineId int;
SET @CruiselineId = (SELECT [CruiselineId] FROM [dbo].Ships
WHERE ShipId = @ShipId);
---- CREATING COPY OF Ships
INSERT INTO Ships (
[CruiselineId], [Name], [ShipCode], [ClassId],
[guestNumber], [staffNumber], [creationYear],
[weight], [length], [passagerDeck], [handicapCabins], [nationality]
)
SELECT
[CruiselineId], @ShipName, @ShipCode, [ClassId],
[guestNumber], [staffNumber], [creationYear],
[weight], [length], [passagerDeck], [handicapCabins], [nationality]
FROM [dbo].Ships
WHERE ShipId = @ShipId;
DECLARE @NewShipId int = SCOPE_IDENTITY();
---- CREATING COPY OF Deck
INSERT INTO Deck (
ShipId, DeckLevel, [Name],
NameSE, NameNO
)
SELECT
@NewShipId, DeckLevel, [Name],
NameSE, NameNO
FROM dbo.Deck dd
WHERE ShipId = @ShipId;
---- CREATING COPY OF Facilities
INSERT INTO Facilities (
ShipId, FacilityCategoryId, [Name],
[Type], MinAge, MaxAge, PriceRange,
[Description], DescriptionNo, DescriptionSe,
NameSE, NameNO
)
SELECT
@NewShipId, FacilityCategoryId, [Name],
[Type], MinAge, MaxAge, PriceRange,
[Description], DescriptionNo, DescriptionSe,
NameSE, NameNO
FROM dbo.Facilities ff
WHERE ShipId = @ShipId;
---- CREATING COPY OF FacilitiesToDeck
INSERT INTO FacilitiesToDeck (
DeckId,
FacilitiesId,
ShipId
)
SELECT fd.DeckId,
bse.FacilitiesId,
@NewShipId
FROM FacilitiesToDeck fd
JOIN dbo.Facilities f on fd.FacilitiesId = f.FacilitiesId
JOIN dbo.Facilities bse on f.[Name] = bse.[Name] and bse.ShipId = @NewShipId
JOIN dbo.Deck d on d.ShipId = @NewShipId
WHERE fd.ShipId = @ShipId;
END
這是我當前代碼的結果：
設施到甲板
| FacilitiesToDeckId | ShipId | FacilitiesId | DeckId |
| ------------------ | ------ | ------------ | ------ |
| 1 | 1 | 1 | 1 |
| 2 | 1 | 2 | 1 |
| 3 | 1 | 3 | 2 |
| 4 | 1 | 4 | 2 |
| 5 | 1 | 5 | 3 |
| 6 | 1 | 5 | 4 |
| 7 | 2 | 6 | 5 |
| 8 | 2 | 6 | 6 |
| 9 | 2 | 6 | 7 |
| 10 | 2 | 7 | 5 |
| 11 | 2 | 7 | 6 |
| 12 | 2 | 7 | 7 |
| 13 | 2 | 8 | 5 |
| 14 | 2 | 8 | 6 |
| 15 | 2 | 8 | 7 |
...
...
正如您希望在上面所說的那樣，我當前的程式最終會在每個新甲板上插入所有新設施，而不是在預期的甲板上插入新設施。現在，當我查看我的代碼時，這個輸出是有意義的，但我仍然無法想出一個解決方案來做我打算做的事情。我希望新插入的行具有來自和FacilitiesToDeck的新 ID 。FacilitiesDeck
有人有想法嗎？
編輯：添加了我自己的嘗試及其結果輸出。
uj5u.com熱心網友回復：
這是一個例子：
drop table #Facilities;
create table #Facilities
( FacilitiesId int identity(1,1)
, ShipId integer
, fName varchar(10))
;
set identity_insert #Facilities on;
insert into #Facilities( FacilitiesId , ShipId , fName )
values
( 1, 1 , 'Facility 1' )
,( 2, 1 , 'Facility 2' )
,( 3, 1 , 'Facility 3' )
,( 4, 1 , 'Facility 4' )
,( 5, 1 , 'Facility 4' )
;
set identity_insert #Facilities off;
drop table #Deck;
create table #Deck
(DeckId int identity(1,1)
,ShipId int
,DeckLevel int)
;
set identity_insert #Deck on;
insert into #Deck(DeckId , ShipId , DeckLevel)
values
( 1 , 1 , 1 )
,( 2 , 1 , 2 )
,( 3 , 1 , 3 )
,( 4 , 1 , 4 )
;
set identity_insert #Deck off;
drop table #FacilitiesToDeck;
create table #FacilitiesToDeck
( FacilitiesToDeckId int identity (1,1)
, ShipId int
, FacilitiesId int,
DeckId int);
set identity_insert #FacilitiesToDeck on;
insert into #FacilitiesToDeck ( FacilitiesToDeckId, ShipId , FacilitiesId , DeckId )
values
(1 , 1 , 1 , 1 )
, (2 , 1 , 2 , 1 )
, (3 , 1 , 3 , 2 )
, (4 , 1 , 4 , 2 )
, (5 , 1 , 5 , 3 )
, (6 , 1 , 5 , 4 )
set identity_insert #FacilitiesToDeck off;
------------------ Start
-- These are hard coded here, but you can use the same insert...output technique to get the new Ship Id
declare @OldShipId int=1,
@NewShipId int=2
;
--- These table variables will hold old-new id maps
declare
@FacilitiesIdMap table
(OldFacilitiesId int,
NewFacilitiesId int);
declare
@DeckIdMap table
(OldDeckId int,
NewDeckId int);
declare @ThisId int;
begin tran
-- Copy facilities, and keep track of old-to-new ids
select @ThisId=min(FacilitiesId) from #Facilities where ShipId=@OldShipId;
while @ThisId is not null begin
insert into #Facilities (ShipId, fName)
output @ThisId, inserted.FacilitiesId into @FacilitiesIdMap
select @NewShipId, fName
from #Facilities
where FacilitiesId=@ThisId;
-- Get the next id
select @ThisId=min(FacilitiesId) from #Facilities where ShipId=@OldShipId and FacilitiesId > @ThisId;
end;
-- Copy decks, and keep track of old-to-new ids
select @ThisId=min(DeckId) from #Deck where ShipId=@OldShipId;
while @ThisId is not null begin--
insert into #Deck (ShipId, DeckLevel)
output @ThisId, inserted.DeckId into @DeckIdMap
select @NewShipId, DeckLevel
from #Deck
where DeckId=@ThisId;
-- Get the next id
select @ThisId=min(DeckId) from #Deck where ShipId=@OldShipId and DeckId > @ThisId;
end;
insert into #FacilitiesToDeck ( ShipId , FacilitiesId , DeckId )
select @NewShipId, F.NewFacilitiesId, D.NewDeckId
from @FacilitiesIdMap F
inner join
#FacilitiesToDeck F2D
on F.OldFacilitiesId=F2D.FacilitiesId
inner join
@DeckIdMap D
on D.OldDeckId=F2D.DeckId
where F2D.ShipId=@OldShipId
select * from #Facilities
select * from #Deck
select * from #FacilitiesToDeck
commit
------ End
我沒有聲稱它是性能最好或最好的解決方案，但如果您的 xxxId 列是標識列，這將起作用。我沒有添加任何錯誤檢查；理想情況下，如果有任何失敗，您應該有一個 try-catch 塊并回滾所有插入。身份插入可以模擬您的示例資料，同時具有身份列。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/481358.html