asp.net core docker SQL script to insert into many to many table

Text Article
http://csharp-video-tutorials.blogspo...

Slides
http://csharp-video-tutorials.blogspo...

SQL Server Interview Questions and Answers text articles & slides
http://csharp-video-tutorials.blogspo...

SQL Server Interview Questions and Answers playlist
https://www.youtube.com/playlist?list...

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenka...

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatAr...

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#'

asp.net core docker SQL script to insert into many to many table
568 Likes	568 Dislikes
112,740 views views	524K followers
Science & Technology	Upload TimePublished on 6 Feb 2017

Related keywords

wcf vs web api,ado.net core,sql server management studio,webkinz,webadvisor,craigslist nj,wcf one piece,asp.net core 3,ajax players,weber grill parts,webtoon,tutorials by hugo,csharp corner,mvc design pattern,asp.net machine account,servers for minecraft,asp.net core dependency injection,ado.net tutorial,services angular,ajax ontario,asp.net mvc tutorial,asp.net cos'è,csharp assembly,tutorialspoint python,sql join,services briefcase,asp.net core web api,ajax jquery,wccftech,craigslist ny,asp.net zero,csharp foreach,server memes,sql date format,services online,chase,serverless architecture,server resume,wcf c#,server books,tutorialspoint javascript,mvcc connect,ado.net mysql,services technologies gps,ajax deadpool,server jobs,cvs,website,mvc tutorial,costco hours,wcf service application,tutorialspoint spring,serverless,wcf soap,wcf cat,cool math games,wcf test client,services & training hse colombia sas,servicestack,citibank,asp.net core identity,sql union,ajax parking,sql database,asp.net core logging,mvconnect,cunyfirst,asp.net guida,wcf nba,csharp download,wcfi foundation,csharp online,wcf authentication,tutorials near me,http://asp.net,server 2019,chernobyl,ado.net descargar,web of dreams,serverminer,ajax cleaner,ado.net visual studio 2019,webassign,ado.net vs entity framework,ado.net visual studio 2017,csharp list,sql like,asp.net mvc,asp.net core tutorial,sqlite,wcfm,ajax roster,mvc architecture,http://ado.net,asp.net core mvc,ajax soccer,server hosting,wcfi,ajax dish soap,capital one,server rack,tutorialspoint html,csharp interface,craigslist,webroot,tutorialspoint reactjs,ajax request,wcf dragon ball,asp.net core 2.2,tutorialspoint python 3,sql developer,webster,services transmission company sas,sql group by,asp.net core signalr,services manager,mvc framework,ajax paving,mvc near me,tutorialspoint spring boot,mvc map,csharp online compiler,asp.net download,sql between,ado.net c# pdf,services tag dell,wcf 2019 nba,csharp switch,ado.net ventajas y desventajas,csharpstar,wcf tutorial,tutorialspoint,ajax meaning,csharp-video-tutorials.blogspot,tutorials dojo,central park 5,csharp string format,ado.net c#,asp.net core github,server status,ajax fc,server jobs nyc,asp.net core swagger,sql formatter,credit karma,services group,server error in '/' application,services windows,asp.net core 3.0,sql injection,tutorialspoint c#,wcf ria services,calculator,ado.net entity data model,sql insert,tutorialspoint tableau,services google play apk,sqlyog,asp.net core 3 release date,sql server,server job description,tutorials by a,servicenow,webcam,mvc hours,webmd symptom,csharp array,csharp enum,ajax call,asp.net core 2. guida completa per lo sviluppatore,asp.net core,server pro,server status ffxiv,cheap flights,webcrims,asp.net core hosting,services sas,tutorialspoint java,tutorialspoint java compiler,webmd,csharp to json,college football,ado.net dataset,csharp dictionary,cnn,website builder,tutorialspoint sql,asp.net web api,server side rendering,weber grills,sql server 2017,tutorialspoint spark,mvc nj,ado.net oracle,asp.net core download,csharp to vb.net,webster bank,webstaurant,tutorialsystems,ajax post,services fms publish announcement,services.msc no abre,ajax jersey,csharp operator,asp.net core razor pages,server duties,asp.net core environment variables,csharp random,century 21,services consultores,services consulting,mvcsd,services.msc,mvcsc,ado.net pdf,asp.net core configuration,ajax marvel,sql update,asp.net tutorial,mvc medical,ado.net entity data model visual studio 2019,wcfs international curriculum,mvc2,ado.net entity data model visual studio 2017,chase online,wcf api,costco,server jobs near me,webex,sql meaning,sql tutorial,sql commands,ado.net entity framework,ajax tavern,tutorialsteacher,ajax javascript,services desk,ajax greek,csharp tutorial,mvc pattern,ado.net sql server,ado.net connection,asp.net identity,mvcu,asp.net core middleware,wcf web service,mvc webadvisor,web store,mvcc,webmail,mvci,mvctc,