0

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?

0

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
  • 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. – user3323654 May 5 '15 at 17:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.