Skip to content
Advertisement

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

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)

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.

Advertisement

Answer

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.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement