multi-multi-multiple Insert with subquery???

[jmjyiannis]

Hello, experts, friends and masters.
I get into the point!
I have a huge (for me) problem.
I have some tables in a database. Lessons, Students, Teachers, Courses and AttendedLessons.
The last table (attendedLessons) has the following collumns:
AttendedLessonID
LessonID,
StudentID,
Mark,
CourseID,
TeacherID.
I want to create an insert query which will take the courseID and the StudentID from the user and it will insert in the AttendedLessons records for all of the Lessons of this specific Course of this specific Student of a selected Teacher.
I have tried this....
insert into AttendedLessons (lessonID,CourseID,StudentID,TeacherID)
(select lessonID,CourseID,StudentID,TeacherID from
(select lessonID, CourseID from Lesson where CourseID='(anything that user gives)'),
(studentID='(anything that user gives)'),
(TeacherID='(anything that user gives)');

but Many different problems occured.
Please...... any suggestion?????

 

[ManicCW]

if you are using sql server you can use stored procedure.

 

[jmjyiannis]

and how should I create the stored procedure? I mean.... Which should be its syntax?

Thank you for your reply!

 

[ManicCW]

what version of sql server are you using?

 

[jmjyiannis]

sql server 2000

 

[ManicCW]

in visual studio opet server explorer. connect to your database and expant it in tree. right click stored proceured folder and choose new stored procedure. to lean about sp's do some search on this forum or google.

 

[jmjyiannis]

although I know all this procedure.....
thank you for your response. I appreciate it a lot.

Simply, I was wondering only about the syntax. how to input the variables e.t.c.

 

[jmjyiannis]

Solution:
Just in case that anyone is in the same position in which I was..... the Solution comes from a multiple insert-select subquery:


cmdSelect = New SqlCommand("insert into attendedLessons (LessonID,StudentID,TeacherID,CourseID)
(select distinct lessonID, studentid, PersonID, courseid from Lesson, student, Personnel where Courseid='" & Request.QueryString("CourseID") & "' and studentID='" & ID & "' and PersonID='" & InstructorID & "')", conn)

The result will be to insert as many records the table "lesson" has with the courseID foreignKEY =Request.QueryString("CourseID") with the ID of the student and the ID of the teacher.

Thanks ManicCW for your attention!