How do I insert multiple rows with many to many relations into ‘Table1’ from ‘Table1’?

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.

Answer

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.