使用来自另一个表的外键将批量数据插入到两个相关的表中

问题描述

我已经将一些数据从Excel文件导入到临时SQL表中.然后，我尝试将所有行插入到两个相关的表中.就像这样:我的数据库中有Event和Actors表，它们之间具有多对多关系.演员已经添加.我想将所有事件添加到事件"表中，然后将每个事件的相关性(ActorId)添加到EventActors表中.(dbo.TempTable具有标题"，"ActorId"列)

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

这段代码运行时，所有事件都插入到事件中，但是由于外键错误，关系没有插入到EventActors中.

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

我认为应该有一个循环.但是我很困惑.我不想为此编写C#代码.我知道在SQL Server中将有一个简单但高级的解决方案.感谢您的帮助.

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.

推荐答案

使用输出子句可以使用合并语句，以允许从源表和目标表中进行捕获.

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.

请注意，每行需要一个唯一的ID，并且假定临时表中的1行在Event和EventActor表中都创建了1行.

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;

这篇关于使用来自另一个表的外键将批量数据插入到两个相关的表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！