SQL script to insert into many to many table

Text Article

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

Slides

SQL Server Interview Questions and Answers text articles & slides

SQL Server Interview Questions and Answers playlist

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

In this video we will discuss how to insert data into a table that has many-to-many relationship

Create table Students
(
Id int primary key identity,
StudentName nvarchar(50)
)
Go

Create table Courses
(
Id int primary key identity,
CourseName nvarchar(50)
)
Go

Create table StudentCourses
(
StudentId int not null foreign key references Students(Id),
CourseId int not null foreign key references Courses(Id)
)
Go

Students - Id column is identity column
Courses - Id column is identity column
StudentCourses - StudentId and CourseId columns are foreign keys referencing Id column in Students and Courses tables

As you can see, StudentCourses is a bridge table that has many to many relationship with Students and Courses tables. This means a given student can be enrolled into many courses and a given course can have many students enrolled.

Below is the question asked in an interview for SQL Server Developer role.

Write a SQL script to insert data into StudentCourses table. Here are the rules that your script should follow.
1. There will be 2 inputs for the script
Student Name - The name of the student who wants to enroll into a course
Course Name - The name of the course the student wants to enroll into

2. If the student is already in the Students table, then use that existing Student Id. If the student is not already in the Students table, then a row for that student must be inserted into the Students table, and use that new student id.

3. Along the same lines, if the course is already in the Courses table, then use that existing Course Id. If the course is not already in the Courses table, then a row for that course must be inserted into the Courses table, and use that new course id.

4. There should be no duplicate student course enrollments, i.e a given student must not be enrolled in the same course twice. For example, Tom must not be enrolled in C# course twice.

Answer : To avoid duplicate student course enrollments create a composite primary key on StudentId and CourseId columns in StudentCourses table. With this composite primary key in place, if someone tries to enroll the same student in the same course again we get violation of primary key constraint error.

Alter table StudentCourses
Add Constraint PK_StudentCourses
Primary Key Clustered (CourseId, StudentId)

Here is the SQL script that inserts data into the 3 tables as expected

Declare @StudentName nvarchar(50) = 'Sam'
Declare @CourseName nvarchar(50) = 'SQL Server'

Declare @StudentId int
Declare @CourseId int

-- If the student already exists, use the existing student ID
Select @StudentId = Id from Students where StudentName = @StudentName
-- If the course already exists, use the existing course ID
Select @CourseId = Id from Courses where CourseName = @CourseName

-- If the student does not exist in the Students table
Begin
-- Insert the student
-- Get the Id of the student
Select @StudentId = SCOPE_IDENTITY()
End

-- If the course does not exist in the Courses table
Begin
-- Insert the course
-- Get the Id of the course
Select @CourseId = SCOPE_IDENTITY()
End

-- Insert StudentId & CourseId in StudentCourses table

If required, we can very easily convert this into a stored procedure as shown below.

Create procedure spInsertIntoStudentCourses
@StudentName nvarchar(50),
@CourseName nvarchar(50)
as
Begin

Declare @StudentId int
Declare @CourseId int

Select @StudentId = Id from Students where StudentName = @StudentName
Select @CourseId = Id from Courses where CourseName = @CourseName

Begin
Select @StudentId = SCOPE_IDENTITY()
End

Begin
Select @CourseId = SCOPE_IDENTITY()
End

End

[CaptainSlowbeard]

Dude... don't know if you still check the comments, but I can't thank you enough. Can't believe how few tutorials there are on actually implementing link tables, and this one is perfect - you're an awesome teacher 👍

[krismaly]

Awesome

I enjoyed watching this video and recommend others.

Host has very good experience and control on SQL.
Explains the functionality clear and crisply step by step and everyone could understand even non-sql person.

Thanks for educating the community and appreciate your volunteership in educating the world

Thanks a bunch

[dsy]

Thank you very much. This this what I wanted. Very clear and lot of valuable information. keep up the good work.

[surajwankhade]

Thank you kudvenkat! Your videos have helped me immensely in cracking technical job interviews.

[DaveMcTKD]

Best example I have seen for this topic. Very good.

[sadamshah]

Thank you sir for teaching in such an easiest way great concepts. I love watching your tutorials.

[Eric-imnn]

Sir, your video is always clear and to the point, really like it. Thanks!

[mdziaulhaq]

Your explanation is awesome sir.... it's called a good teaching.

[googler]

Thank you Venkat, for your time and such good turtorial videos it will be helpful for somany beginners. Thanks

[manishprabhakaran]

Very clear explanation 👏. But can a student get enrolled for 2 courses at the same time? Instead of executing the script for 1 Student and 1 course, I need it as 1 Student and 2 courses at the same time. So now 2 courses will get inserted into the Courses table and 2 records will appear in the StudentCourse table.
Hope I doubt gets clarified . Thank you

[TimPauley]

Great video. Thanks for including the script in the description. I also created a script to Remove FK, Truncate Tables, Replace FK. However, to get the script to work you would have to name your FK (since you didn't they are auto generated). Maybe an improvement for a later version.

[siripreethu]

Hi venkat, I have gone through all the videos of SQL server! It’s very helpful and perfect presentation😊
I have requested you long time ago to upload videos fir SSIS as well... but I didn’t find in your complete playlist! 😕Could you please upload/teach us SSIS as well?

[namratasingh]

Thanks for the beautiful explanation :) Is there a way to do this directly for a table instead of single single values.

[dinasamer]

thanks a lot about your effort i'm a big fan for your channel

[avinash]

Sir just would letting you know my small suggestion.. We can use IF NOT EXISTS prior to Insertion of data so there wouldn't be need of using composite keys and no duplicate data

[sufyanalishani]

Thanks #venkat, best wishes from Pakistan.

[lesliepalmer]

Love this and it worked! But now how do I write an update/edit stored procedure without messing up data in bridge table?

[rogerbreton]

Thank you for taking the time to explain. Your videos are alway invaluable. Is there a way to *automatically* insert/delete into a bridge table? I understand how to do this manually.

[assassinsrose]

Awesome Video, It was very helpful. Thank you.

[karangagrani]

Very Helpful and explained quite well. Keep it up