Insert bulk data into two related tables with foreign keys from another table

Question

I have imported some data to a temp SQL table from an Excel file. Then I have tried to insert all rows to two related tables. Simply like this: There are Events and Actors tables with many to many relationship in my database. Actors are already added. I want to add all events to Events table and then add relation(ActorId) for each event to EventActors tables. (dbo.TempTable has Title, ActorId columns)

insert into dbo.Event (Title) 
Select Title 
From dbo.TempTable

insert into dbo.EventActor (EventId, ActorId) 
Select SCOPE_IDENTITY(), ActorId                       --SCOPE_IDENTITY() is for EventId
From dbo.TempTable

When this code ran, all events inserted into Events, but the relations didn't inserted into EventActors because of Foreign Key error.

I think there should be a loop. But I am confused. I don't want to write C# code for this. I know there would be a simple but advanced solution trick for this in SQL Server. Thanks for your help.

Answer 1

Use the output clause to capture the new IDs, with a merge statement to allow capture from both source and destination tables.

Having captured this information, join it back to the temp table for the second insert.

Note you need a unique id per row, and this assumes 1 row in the temp table creates 1 row in both the Event and the EventActor tables.

-- Ensure every row has a unique id - could be part of the table create
ALTER TABLE dbo.TempTable ADD id INT IDENTITY(1,1);

-- Create table variable for storing the new IDs in
DECLARE @NewId TABLE (INT id, INT EventId);

-- Use Merge to Insert with Output to allow us to access all tables involves
-- As Insert with Output only allows access to columns in the destination table
MERGE INTO dbo.[Event] AS Target
USING dbo.TempTable AS Source
ON 1 = 0 -- Force an insert regardless
WHEN NOT MATCHED THEN
    INSERT (Title)
    VALUES (Source.Title)
    OUTPUT Source.id, Inserted.EventId
    INTO @NewId (id, EventId);

-- Insert using new Ids just created
INSERT INTO dbo.EventActor (EventId, ActorId) 
    SELECT I.EventId, T.ActorId
    FROM dbo.TempTable T
    INNER JOIN @NewId I on T.id = T.id;