CODEWITHSUNDEEP
sql-serverdatabasestored-proceduresinsertmany-to-many
Nishant Singh
Nishant Singh

Reputation: 1354

SQL Server : insert multiple records into junction table

I have two tables Colleges & Facilities and I have a many to many relationship between these two tables via a junction table.

Colleges table:

   id
   name
   location
   facilities

Facilities table:

       name
       id

Junction table:

       college_id
       facility_id

The data that is coming from frontend is a json data in below format

{
  "name": "ABC College",
  "location": "ABC Location",
  "facilities" [1,2,3,4,5,6] // Id's of facilities
}

Assuming that facilities already exists in database.

First i want to insert the data of college into COLLLEGE table & after that i want to update the junction table as below, where 1 is the id of newly inserted row in COLLEGE table

college id | facility id
   1            1
   1            2
   1            3
   1            4
   .... and so on

How can i create a stored procedure for the same. Where i can update all the records in one go or by using the some loop of any other better method.

Following methods i have explored

  1. While Loop
  2. String Split
  3. Table Valued Parameter

I'm not able to use first two methods properly, also I don't want to use table valued parameter, because database might change in future

Is there any better way to achieve this using stored procedure, please help I'm struggling with this from last 10 days. Thanks

Note:

  1. Want to avoid calling multiple stored procedures from backend code
  2. Want to avoid calling stored procedure in loop from backend code.
  3. Don't want to use temporary table method

Update 1: Json is not passed directly into the stored procedure, i want the stored procedure something like below if possbile

create procedure spCreateCollege (
  @name varchar(100),
  @location varchar(100),
  @facilityList varchar(500) // Array or comma seprated string (if possible)
)

Upvotes: 0

Views: 540

Answers (2)

seanb
seanb

Reputation: 6685

OK.. key logic here is to

  • Insert the new college
  • Get its ID
  • Insert into the College_facilities table

If you are confident about your data - especially the facilityList being comma separated, you can use it in dynamic SQL as part of an IN statement.

Key SQL is below, with full approach in this db<>fiddle

INSERT INTO Colleges (name, location, facilityList)
    SELECT @name, @location, @facilityList

SET @college_id = SCOPE_IDENTITY()

SET @CustomSQL = 
N'INSERT INTO College_Facilities (college_id, facility_id)'
+N' SELECT ' + LTRIM(STR(@college_id)) + ', f.id'
+N' FROM Facilities f'
+N' WHERE f.id IN (' + @facilityList + ')'

EXEC (@CustomSQL)

Notes

  • This demonstrates a solution, but does no data cleaning, checking if data exists, or anything similar. You should do this.
  • Do not name stored procedures sp... as this is used by Microsoft special stored procedures.

Upvotes: 1

GMB
GMB

Reputation: 222432

If I follow you correctly, you can use JSON functions. Assuming that your json string is given as parameter @js:

insert into college_facilities (college_id, facility_id)
select c.id, f.value 
from colleges c
cross join openjson(@js, '$.facilities') as f
where c.name =  json_value(@js, '$.location')

The logic is to search the college table by name to retrieve the corresponding id, and to use openjson() to unnest the array of facilities id.

Upvotes: 1

Related Questions