如何插入選擇語句的多個返回？

我有 3 張桌子。ClassList, Student& Faculty. 我正在嘗試將教師分配給特定的班級，例如，我將“John Doe”分配給“Section1”部分，其中“Section1”存在於Student表格列中。

結果：

學生桌

StudentID       Name    Section
   1            user1    Section1
   2            user2    Section1
   3            user3    Section2

教師表

FacultyID       Name       Subject
   1            Faculty1   Subject ABC
   2            Faculty2   Subject DEF

類列表表

 ClassListID        StudentID      FacultyID    ModifiedDate

我的插入語句是這樣的：

INSERT INTO ClassList 
VALUES 
( 
   (
       SELECT Student.StudentID 
       FROM Student 
       WHERE Student.Section = 'Section1'
   ), 
   (
       SELECT Faculty.FacultyID 
       FROM Faculty 
       WHERE Faculty.FirstName = @FacultyName 
       OR Faculty.LastName = @FacultyName
   ), 
   GETDATE()
);

我知道Student表中的 select 語句返回很多，所以我想知道是否有辦法將它的所有返回查詢添加到ClassList表中？

如果第一個選擇返回的所有學生都需要與第二個選擇返回的所有學院相結合，那麼您只需將兩個子集交叉連接並從結果集中進行選擇。您可以使用經典的逗號連接或顯式CROSS JOIN語法：

1. 逗號連接：

INSERT INTO
 dbo.ClassList (StudentID, FacultyID, ModifiedDate)
SELECT
 s.StudentID
, f.FacultyID
, GETDATE()
FROM
 (
   SELECT Student.StudentID 
   FROM dbo.Student 
   WHERE Student.Section = 'Section1'
 ) AS s
, (
   SELECT Faculty.FacultyID 
   FROM dbo.Faculty 
   WHERE Faculty.FirstName = @FacultyName 
   OR Faculty.LastName = @FacultyName
 ) AS f
;

2. CROSS JOIN:

INSERT INTO
 dbo.ClassList (StudentID, FacultyID, ModifiedDate)
SELECT
 s.StudentID
, f.FacultyID
, GETDATE()
FROM
 (
   SELECT Student.StudentID 
   FROM dbo.Student 
   WHERE Student.Section = 'Section1'
 ) AS s
 CROSS JOIN
 (
   SELECT Faculty.FacultyID 
   FROM dbo.Faculty 
   WHERE Faculty.FirstName = @FacultyName 
   OR Faculty.LastName = @FacultyName
 ) AS f
;

這些變體正在使用您的子選擇而沒有任何更改。您還可以先重寫查詢並交叉連接表，然後再過濾它們WHERE：

INSERT INTO
 dbo.ClassList (StudentID, FacultyID, ModifiedDate)
SELECT
 s.StudentID
, f.FacultyID
, GETDATE()
FROM
 dbo.Student AS s
, dbo.Faculty AS f
-- or: CROSS JOIN dbo.Faculty AS f
WHERE
 s.Section = 'Section1'
 AND (f.FirstName = @FacultyName 
   OR f.LastName = @FacultyName)
;

請注意，您需要將OR-ed Faculty 過濾器放在括號中，以便將它們與 Student 過濾器正確組合。沒有括號，由於 Transact-SQL 中邏輯運算符的優先規則，結果將與所需的邏輯不匹配：AND將首先評估，OR其次，而您希望它以相反的方式進行，因此括號。

由於您在這兩個表中似乎沒有任何共同資訊，因此我建議您執行以下操作distinct cross apply：

INSERT INTO ClassList (StudentID, FacultyID, ModifiedDate)
SELECT DISTINCT student.studentID
   ,faculty.FacultyID
   ,getdate()
FROM Student
CROSS APPLY faculty

引用自：https://dba.stackexchange.com/questions/100532