Sql-server – How to insert multiple returns of the select statement

insertquerysql server

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?

Best Answer

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.

Related Question