Блог

Главная - Вопросы по программированию - SQL Server: вставка нескольких записей в таблицу соединений

SQL Server: вставка нескольких записей в таблицу соединений

#sql-server #database #stored-procedures #insert #many-to-many

#sql-сервер #База данных #хранимые процедуры #вставить #многие ко многим

Вопрос:

У меня есть две таблицы Colleges amp; Facilities и у меня есть связь «многие ко многим» между этими двумя таблицами через таблицу соединений.

Таблица колледжей:

    id
   name
   location
   facilities

Таблица объектов:

        name
       id

Таблица соединений:

        college_id
       facility_id

Данные, поступающие из интерфейса, представляют собой данные json в формате ниже

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

Предполагая, что средства уже существуют в базе данных.

Сначала я хочу вставить данные колледжа в таблицу COLLEGE, а после этого я хочу обновить таблицу соединений, как показано ниже, где 1 — идентификатор недавно вставленной строки в таблице COLLEGE

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

Как я могу создать хранимую процедуру для того же. Где я могу обновить все записи за один раз или с помощью некоторого цикла любого другого лучшего метода.

Я исследовал следующие методы

  1. Цикл While
  2. Разделение строки
  3. Табличный параметр

Я не могу правильно использовать первые два метода, также я не хочу использовать параметр с табличным значением, потому что база данных может измениться в будущем

Есть ли лучший способ добиться этого с помощью хранимой процедуры, пожалуйста, помогите, я борюсь с этим последние 10 дней. Спасибо

Примечание:

  1. Хотите избежать вызова нескольких хранимых процедур из серверного кода
  2. Хотите избежать вызова хранимой процедуры в цикле из серверного кода.
  3. Не хотите использовать метод временной таблицы

Обновление 1: Json не передается непосредственно в хранимую процедуру, я хочу, чтобы хранимая процедура выглядела примерно так, как показано ниже, если возможно

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

Ответ №1:

Если я правильно вас понял, вы можете использовать функции JSON. Предполагая, что ваша строка json задана в качестве параметра @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')

Логика состоит в том, чтобы выполнить поиск в таблице колледжа по имени, чтобы получить соответствующее id , и использовать openjson() для отмены идентификации массива идентификаторов объектов.

Комментарии:

1. Привет, я только что обновил вопрос, мы не передаем json непосредственно в хранимую процедуру. Спасибо за это, я попробую напрямую передать json 🙂

Ответ №2:

ХОРОШО .. ключевая логика здесь заключается в

  • Вставьте новый колледж
  • Получить его идентификатор
  • Вставка в таблицу College_facilities

Если вы уверены в своих данных, особенно в том, что facilityList разделен запятыми, вы можете использовать его в динамическом SQL как часть инструкции IN .

Ключевой SQL приведен ниже, с полным подходом в этом db<>скрипте

 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)

Примечания

  • Это демонстрирует решение, но не выполняет очистку данных, проверку наличия данных или что-либо подобное. Вы должны сделать это.
  • Не называйте хранимые процедуры sp …, поскольку это используется специальными хранимыми процедурами Microsoft.