How to insert multiple returns of the select statement?

Question

I have 3 tables. ClassList, Student & Faculty. I'm trying to assign the faculty to a specific class, e.g. I'm assigning "John Doe" to the section "Section1" where this "Section1" exists in the Student table column.

Outcome:

Student table

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

Faculty table

 FacultyID Name Subject
 1 Faculty1 Subject ABC
 2 Faculty2 Subject DEF

ClassList table

 ClassListID StudentID FacultyID ModifiedDate

My insert statement is like this:

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()
);

I know that the select statement in the Student table returning a lot so I'm wondering if there's a way to add all the return query of it into ClassList table?

Answer 1

If all students returned by the first select need to be combined with all faculties returned by the second select, then you just need to cross-join the two subsets and select from the resulting set. You can use either the classic comma join or the explicit CROSS JOIN syntax:

1. Comma 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
   , (
    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
   ;
   

Those variations are using your subselects without any change. You could also rewrite the query and cross-join the tables first before filtering both in the same 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)
;

Note that you need to put the OR-ed Faculty filters in brackets in order to combine them with the Student filter properly. Without the brackets the result would not match the desired logic due to precedence rules for the logic operators in Transact-SQL: AND would be evaluated first, OR second, while you would want it to be the other way round, hence the brackets.

Answer 2

Since you don't appear to have any common information in these two tables, I'd recommend doing a distinct cross apply:

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

• user3323654: What does cross apply do? Yes, I am using SQL Server. My problem is the select statement I've made for the Students has a lot of results and can't be lessen by using distinction because it gives unique results. I want to add all of these results example 5 results to be added in the class list in one insert statement with the faculty.
• Jonathan Fite: Cross Apply is a valid answer, but you neglected to preserve OP's logic about which students and faculty to include. I would suggest expanding on your answer at least that much.