HomeНаука и техникаRelated VideosMore From: kudvenkat

SQL script to insert into many to many table

477 ratings | 96891 views
Text Article http://csharp-video-tutorials.blogspot.com/2017/02/sql-script-to-insert-into-many-to-many.html Slides http://csharp-video-tutorials.blogspot.com/2017/02/sql-script-to-insert-into-many-to-many_6.html SQL Server Interview Questions and Answers text articles & slides http://csharp-video-tutorials.blogspot.com/2014/05/sql-server-interview-questions-and.html SQL Server Interview Questions and Answers playlist https://www.youtube.com/playlist?list=PL6n9fhu94yhXcztdLO7i6mdyaegC8CJwR All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists 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 Use the following statement to execute the stored procedure Execute spInsertIntoStudentCourses 'Tom','C#'
Html code for embedding videos on your blog
Text Comments (56)
Tianqin Guo (2 years ago)
Really nice video. Thanks a lot
kudvenkat (2 years ago)
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 https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd If you need DVDs or to download all the videos for offline viewing please visit http://www.pragimtech.com/order.aspx Slides and Text Version of the videos can be found on my blog http://csharp-video-tutorials.blogspot.com Tips to effectively use my youtube channel. https://www.youtube.com/watch?v=y780MwhY70s If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel. http://www.youtube.com/subscription_center?add_user=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
Bhushan Bhasme (12 days ago)
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...
Planet Review (18 days ago)
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?
Ahmed Araby (1 month ago)
very nice , thanks
Thank you!
Aditya Pal (2 months ago)
How can use update query if we insert data one table to another table..
Fateh new (4 months ago)
This course is helpfull Thank's
потрясающе. жалко что на english - однако все равно - все понятно. в конце даже сделал из запросов хранимую функцию.
Boyuan Li (7 months ago)
Very nice and clear. Thanks!
Feng (9 months ago)
Very clear , excellent
karan gagrani (9 months ago)
Very Helpful and explained quite well. Keep it up
Travis Richmond (11 months ago)
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???
kudvenkat (11 months ago)
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. https://www.youtube.com/watch?v=GKGtOABAO9s
TallCoolDrink (1 year ago)
Your video series are EXCELLENT!
Adeel Malik (1 year ago)
Hello plz tell me how to get all courses related to a specific student . how we will query that ??? Thank You
Trzbne (11 months ago)
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.
Rasmi Mohan (1 year ago)
Wonderful explanation
darpan waghchawre (1 year ago)
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
darpan waghchawre (1 year ago)
how to load specific record by dts in database ? can you please explan me
Sufyan Ali shani (1 year ago)
Thanks #venkat ,best wishes from Pakistan.
farhanaM shaik (1 year ago)
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 ?
Trzbne (11 months ago)
Watch SQL tutorial for beginners by Venkat, there is a good description about this topic in part 109.
Siri preethu (1 year ago)
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?
Rajeev Ranjan (1 year ago)
In a single query how to fetch the all columns of Students , Course and StudentCourses??
Tim Pauley (1 year ago)
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.
D (1 year ago)
Thank you very much. This this what I wanted. Very clear and lot of valuable information. keep up the good work.
shekhar mulye (2 years ago)
Dear Sir, I want to prepare for Microsoft Exam 70-461: Querying Microsoft SQL Server 2012. I need your tip and advice in that how should I Prepare for that in Practically and theoretical way. The Exam is divided into 4 modules: 1. Create Database Objects | 24% 2. Work with Data | 27% 3. Modify Data | 24% 4. Troubleshoot & Optimize | 25% Kindly advice me how should I Prepare for that to pass the exam. I like your videos and it helps a lot. Your the best sir in this world. Thank You Very much, sir. My email - [email protected] I am a software developer. Kindly Waiting for your reply....
Trzbne (11 months ago)
How was your exam? I hope you succeeded. Are Venkat tutorials good preparation for them? Do not give your e-mail address on public places, man. Robots are reading the pages and collecting e-mail addresses and you will receive a lot of spam...
Maryam Pashmi (2 years ago)
Hello, is there anybody knows how to convert this sript in MySQL?
Wonderful! Thank You very much
Saleem Khan (2 years ago)
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?
Makara Chhin (2 years ago)
Thank you so much! That's very useful!
Sudha Chitiprolu (2 years ago)
Very nice videos sir... thanks a lot sir...
reza k (2 years ago)
thank you ;)
Renoir Rapido (2 years ago)
Hope you can also make a video of One to many Relationship
Jay Singamsetty (2 years ago)
Thank you Venkat, for your time and such good turtorial videos it will be helpful for somany beginners. Thanks
NUH ZAKİR (2 years ago)
Thank you. It was helpful.
Hansjörg Reister (2 years ago)
the two insert statements should be wrapped by begin transaction , commit and rollback.
Kavita Jena (2 years ago)
please upload a video on dynamic sql query
Chaudhry Ali Salam (2 years ago)
thank you sir
zxmasha (2 years ago)
Albshr77 (2 years ago)
Thank you
Kris Maly (2 years ago)
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
Thanks a billions.
please upload video on Design pattern.
נתן חורי (2 years ago)
hello , is it ok to add a video on how to update multi database and change them with script
Hafiz Siddiqui (2 years ago)
thank a lot sir, please upload some interview Q & A about MVC
Stefan Skoda (2 years ago)
very useful, like usual :) thanks
UMAROUT (2 years ago)
thank u very much kudvenkat sir
Saurabh Chauhan (2 years ago)
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. :)
Rahul Salinskee (2 years ago)
Sir, If possible, please make tutorial videos to cover WPF including CRUD operation - using MVVM...!!!
mohamed omiera (2 years ago)
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
Muhammad Rehbar Sheikh (2 years ago)
thank you venkat sir.
Wellington Davis (2 years ago)
Some tutorials about MSBI would be nice !!
Peter L (2 years ago)
nice one venkat! i would love to hear your talk avout machine learning in the future!

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.