I'm trying to copy an existing ship, with all of its contents of its child tables (for lack of a better term) and their relations. So far I've got most of the copying down except for the ship's tables that have a many to many relation. An example of my current situation is as follows:
I have two individual tables and a third table that connects them both:
Facilities
| FacilitiesId | ShipId | fName |
| ------------ | ------ | ---------- |
| 1 | 1 | Facility 1 |
| 2 | 1 | Facility 2 |
| 3 | 1 | Facility 3 |
Deck
| DeckId | ShipId | DeckLevel|
| ------ | ------ | -------- |
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
FacilitiesToDeck
| 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 |
As you can see above, it is possible for FacilitiesToDeck to have multiple rows with the same DeckId as there can be multiple facilities on the same deck level. Same goes for FacilitiesId as there can be facilities that spand over multiple deck levels.
Now, when I copy a ship with its child entities I expect the above tables to look like this:
Facilities
| 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 |
Deck
| DeckId | ShipId | DeckLevel|
| ------ | ------ | -------- |
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 2 | 1 |
| 5 | 2 | 2 |
| 6 | 2 | 3 |
FacilitiesToDeck
| 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 |
My stored procedure is meant to insert multiple new rows of data from FacilitiesToDeck into FacilitiesToDeck, but where the ShipId, FacilitiesId and DeckId are the new ID values that I create earlier in my stored procedure for Facilities and Deck.
However, I fail to see (or find on the internet) a solution on how to do this as every value of every new row that's being inserted into FacilitiesToDeck is different, with seemingly no correlation (at least to me and my peers).
The following is a snippet of my current stored procedure:
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
And this the result of my current code:
FacilitiesToDeck
| 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 |
...
...
As you can hopefully tell above, my current procedure ends up inserting all new facilities on every new deck, instead of inserting the new facilities with their intended deck. This output makes sense now when I review my code but I still can't come up with a solution of doing what I intend to do. I want the newly inserted rows FacilitiesToDeck to have the new IDs from Facilities and Deck.
Anyone got an idea?
EDIT: Added my own attempt and its resulting output.
CodePudding user response：
Here is an example:
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
I don't claim that it is the best performing or the nicest solution, but this would work if your xxxId columns are identity columns. I haven't added any error checking; ideally you should have a try-catch block and rollback all inserts, if anything fails. identity inserts are there to simulate your sample data, while having identity columns.