SQL script to insert into many to many table
Vložit
- čas přidán 5. 02. 2017
- Text Article
csharp-video-tutorials.blogspo...
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 CZcams channel. Hope you can help.
/ @aarvikitchen5572
Slides
csharp-video-tutorials.blogspo...
SQL Server Interview Questions and Answers text articles & slides
csharp-video-tutorials.blogspo...
SQL Server Interview Questions and Answers playlist
• SQL Server Interview Q...
All Dot Net and SQL Server Tutorials in English
czcams.com/users/kudvenka...
All Dot Net and SQL Server Tutorials in Arabic
/ kudvenkatarabic
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
If (@StudentId is null)
Begin
-- Insert the student
Insert into Students values(@StudentName)
-- Get the Id of the student
Select @StudentId = SCOPE_IDENTITY()
End
-- If the course does not exist in the Courses table
If (@CourseId is null)
Begin
-- Insert the course
Insert into Courses values(@CourseName)
-- Get the Id of the course
Select @CourseId = SCOPE_IDENTITY()
End
-- Insert StudentId & CourseId in StudentCourses table
Insert into StudentCourses values(@StudentId, @CourseId)
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
If (@StudentId is null)
Begin
Insert into Students values(@StudentName)
Select @StudentId = SCOPE_IDENTITY()
End
If (@CourseId is null)
Begin
Insert into Courses values(@CourseName)
Select @CourseId = SCOPE_IDENTITY()
End
Insert into StudentCourses values(@StudentId, @CourseId)
End - Věda a technologie
Really nice video. Thanks a lot
Thank you very much for taking time to give feedback. This means a lot. I am very glad you found the videos useful.
I have organised all the Dot Net & SQL Server videos in to playlists, which could be useful to you
czcams.com/users/kudvenkatplaylists?view=1&sort=dd
If you need DVDs or to download all the videos for offline viewing please visit
www.pragimtech.com/order.aspx
Slides and Text Version of the videos can be found on my blog
csharp-video-tutorials.blogspot.com
Tips to effectively use my youtube channel.
czcams.com/video/y780MwhY70s/video.html
If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel.
czcams.com/users/kudvenkat
If you like these videos, please click on the THUMBS UP button below the video.
May I ask you for a favor. I want these tutorials to be helpful for as many people as possible. Please share the link with your friends and family who you think would also benefit from them.
Good Luck
Venkat
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 👍
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
Thank you kudvenkat! Your videos have helped me immensely in cracking technical job interviews.
Thank you very much. This this what I wanted. Very clear and lot of valuable information. keep up the good work.
Thank you Venkat, for your time and such good turtorial videos it will be helpful for somany beginners. Thanks
Your video series are EXCELLENT!
Best example I have seen for this topic. Very good.
Sir, your video is always clear and to the point , really like it. Thanks!
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.
Thank you so much! That's very useful!
Thank you sir for teaching in such an easiest way great concepts. I love watching your tutorials.
nice one venkat! i would love to hear your talk avout machine learning in the future!
Awesome Video, It was very helpful. Thank you.
Thanks #venkat ,best wishes from Pakistan.
Very Helpful and explained quite well. Keep it up
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?
Your explanation is awesome sir.... it's called a good teaching.
Wonderful! Thank You very much
thank a lot sir, please upload some interview Q & A about MVC
Thank you. It was helpful.
Very nice and clear. Thanks!
very useful, like usual :) thanks
thanks a lot about your effort i'm a big fan for your channel
This course is helpfull
Thank's
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.
thank you venkat sir.
thanks alot. it's really helpful
Love this and it worked! But now how do I write an update/edit stored procedure without messing up data in bridge table?
Very clear , excellent
Nice one!
Wonderful explanation
Some tutorials about MSBI would be nice !!
how to delete a student record?for eg student same is enrolled in 3 courses.now if i want to delete sam record from students table it gives error.how to completely delete sam record from both students table and from bridge table StudentCourse at the same time?
and how we can only delete single course of sam while keeping other twos record in the table?
Thanks a billions.
sir if i want to retrieve the data of how many or show me the detail of student enroll in c# or anyother course how can i do that
Hello, is there anybody knows how to convert this sript in MySQL?
Thank you!
Hello sir, I got interview question like how can we optimise query or sp to increase the performance?the ways to speed up the database operations with complicated db operations or queries? Please add video for to optimise database query.....with some proof like query with time periode prior to execution of db query and after execution of db query...
Thats amazing.
great thanks!!!
Very nice!
Sir, If possible, please make tutorial videos to cover WPF including CRUD operation - using MVVM...!!!
Thanks for the beautiful explanation :) Is there a way to do this directly for a table instead of single single values.
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
how to load specific record by dts in database ? can you please explan me
How can use update query if we insert data one table to another table..
thank you ;)
Hope you can also make a video of One to many Relationship
Thank you
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
wow super thank you so much you re the best one in database God bless you but i ve a small issue how i can do insert in 2 table in one to many relation i mean i ve table user and table address in the table user there is the foreign key adressId taht references to the table address with the primary key id i thank you again for your effor & energy
What happens if you add a new student with the same name? How would you modify this query to distinguish the new student from the older one?
I want to use Merge Into to add data to these table. is it possible. If these tables already has some data. I want to add new lookup data to existing data.
Please suggest. How to use Merge .
Thank you
amazing
Hello venkat Sir
I had followed all ur sql tutorial videos . Thank you so much Sir for the videos provided for all of us
I have a doubt Sir
How can we delete only one duplicate record from a table having same rows without using rownumber ?
Watch SQL tutorial for beginners by Venkat, there is a good description about this topic in part 109.
thank you sir
very nice , thanks
In a single query how to fetch the all columns of Students , Course and StudentCourses??
i have a big data from two tables ,how i insert values of those tables into the third table
I recently had an interview and they asked: What do you do if you query a table, say Employee, that returns 30 rows. Then, you query that same table, name it Employee2 and it returns 31 rows. How do you find out why you're getting a different number of rows. Can you help???
Hi Travis - This is a great question. I believe the easiest way to find non matching records between the tables is by joining both of them in such a way you get only non-matching rows between the 2 tables. We covered how to do this in the following video. Hope this helps. Please let me know if there are any questions.
czcams.com/video/GKGtOABAO9s/video.html
dear youtube i wish if you can give us the capability to give some videos more than only one like.actually to give unlimited number of likes
you are more than great
thanks a a lot venkat
please tell us if you are intended to upload videos on application architecture and design patterns
you promised already
and we are still waiting impatient
how would you do this in php using ms sql database
please upload a video on dynamic sql query
hello , is it ok to add a video on how to update multi database and change them with script
Very very useful..but I need one more help..how to populate excel file data instead of sample data?
can you please describe?
please upload video on Design pattern.
how to load specific record by dts in database ? can you please explan me ex: i have to load 1000 record out of 5000 in database
великолепно. жаль что на english - но все равно - все понятно. в конце даже создал из запросов хранимую процедуру.
How does SQL script work to update to many many table?
lets say you wanted to prevent a student from enrolling into more than two courses, how would you go about doing that
If possible sir try online training course which are not available here Pragim is not giving online training Please start it. I would like to enroll in it. :)
Hello plz tell me how to get all courses related to a specific student . how we will query that ??? Thank You
I would use join or inner select. You ask the StudentId from Students table by name, and then select CourseId from StudentsCourses table where StudentId equals from previous result. Then make an inner join with Courses table to include in your select query the CourseName column.
ok but what if you has more than just 1 field "CourseName" but CourseDate, CourseRating etc this won't work this only works just for one field
It's not working this command
How to loop email address one by one in stored procedure