Sql insert into from select sql: SQL INSERT INTO SELECT Statement

SQL — вставить значения с объединенными идентификаторами из другой таблицы | Майк Халс

У вас есть значения, для которых у вас есть идентификаторы в таблице. Вставьте идентификаторы за один раз!

Поиск некоторых идентификаторов (изображение Lucas Pezeta на Pexels)

Что вы делаете, когда наша таблица ожидает идентификаторы, но все, что у нас есть, это строки? Представим, что мы ресторан. Мы храним каждый пункт меню и его ингредиенты в нашей базе данных, чтобы мы могли отслеживать все виды статистики; как часто заказывают блюдо и какие ингредиенты наиболее популярны, например.

Мы хотим сохранить новый рецепт в таблице recipes, но есть проблема: в этой таблице требуется id_ингредиента, а не имя_ингредиента, которое у нас есть в данный момент. Как мы можем вставить эти идентификаторы в таблицу recipes, если у нас есть только имена?

В этой статье основное внимание уделяется хранению этих рецептов и ингредиентов самым рациональным способом с помощью уникальной таблицы ингредиентов. После прочтения этой статьи вы:

  • поймете уникальную таблицу
  • поймете как использовать СКВОЗНУЮ таблицу
  • иметь возможность вставлять только уникальные значения в уникальную таблицу
  • иметь возможность вставлять в таблицу при извлечении идентификаторов из уникальной таблицы

Сначала мы настроим несколько таблиц, а затем перейдем к запросу .

В этом разделе мы определим структуру нашей базы данных и создадим все наши таблицы. Затем мы вставим в него некоторые тестовые данные. После этого мы перейдем к тому, как вставлять объединенные идентификаторы. Обратите внимание, что в этой статье используется Postgres, но одни и те же методы применимы ко всем реляционным базам данных 9.0005

Структура базы данных

Мы определим 3 таблицы: одна содержит ингредиенты, другая содержит рецепты, а третья таблица объединяет их вместе:

Структура базы данных нашего ресторана (изображение автора)

Таблица ингредиентов

Первая мы создадим таблицу ингредиентов и вставим некоторые данные. Проверьте эту статью для запроса, который может вставлять только ингредиенты, которых еще нет в таблице, чтобы убедиться, что мы никогда не получим ошибок. Для этого примера мы будем использовать «обычную» вставку ниже:

Таким образом, наша таблица выглядит так:

Наши недавно вставленные ингредиенты

Таблица рецептов

В этой таблице будут храниться рецепты, которые имеют уникальный идентификатор и имя:

Таблица наших рецептов

Таблица ингредиентов рецептов

Эта таблица соединяет один или несколько рецептов для одного или нескольких ингредиентов. Этот тип таблицы известен как «сквозная таблица»; он служит посредником между двумя таблицами, имеющими отношения «многие ко многим». В рецепте может быть несколько ингредиентов, а один ингредиент может быть частью нескольких рецептов.

Мы соединим их вместе с recipe_id и ингредиентом_id:

T

Теперь все наши таблицы созданы, давайте начнем вставлять!

Наши столы готовы, начинаем готовить! (изображение Maarten van den Heuvel на Pexels)

Давайте перейдем к нашей проблеме. Мы хотим создать несколько записей, которые соединяют запись в таблице рецептов с некоторыми записями в таблице ингредиентов. Для этого нам нужен recipe_id и ингридиент_id. Проблема в том, что у нас нет ингридиентов id, только названия ингредиентов. Как мы можем вставить в таблицу recipe_ingredients? Сейчас мы предполагаем, что знаем recipe_id; значение 1. Следующий запрос решает эту проблему:

Давайте посмотрим и пройдемся по запросу.

  • Строки с 1 по 8; здесь мы определяем набор данных, который хотим вставить. Мы знаем название ингредиента и количество.
  • В строке 9 мы говорим, что хотим вставить recipe_id, ингридиент_ид и количество в таблицу recipe_ingredients. Это наша цель
  • В данных SELECT из наших входных значений (строки с 1 по 8), которые мы определяем как d . Мы присоединяем этот набор данных к таблице ингредиентов, сопоставляя названия ингредиентов.
  • Здесь происходит волшебство: как видите, мы выбираем значение 1 для recipe_id, идентификатор из объединенной таблицы ингредиентов и количество из набора входных данных.

Принимая во внимание recipe_id

Хитрость заключается в том, чтобы определить наши входные данные как набор данных, соединить их с необходимыми таблицами, а затем вставить только идентификаторы. Довольно легко, верно? Давайте попробуем это на более сложном примере, где мы присоединяем не только ингридиент_ид, но и рецепт_ид.

Мы дважды выполняем трюк из предыдущего запроса: мы присоединяем рецепт из входных значений в столбце рецепта в таблице рецептов. Затем мы извлекаем идентификатор рецепта из этой таблицы и вставляем его в recipe_ingredients. Посмотрите результат ниже:

Мы вставили ингридиент_ид, соответствующий правильному рецепту_ид. Идеальный!

В этой статье мы рассмотрели несколько довольно изящных приемов: таблица UNIQUE, таблица THROUGH и вставка объединенных значений. Я надеюсь пролить на них свет. Если у вас есть предложения/пояснения, пожалуйста, прокомментируйте, чтобы я мог улучшить эту статью. А пока ознакомьтесь с другими моими статьями на всевозможные темы, связанные с программированием, например:

  • УДАЛИТЬ В другую таблицу
  • ОБНОВИТЬ В другую таблицу
  • Вставка, удаление и обновление в ОДНОМ выражении
  • ОБНОВЛЕНИЕ ВЫБЕРИТЕ пакет записей
  • Сохранить обновление
  • Вставка в УНИКАЛЬНУЮ таблицу

Удачного кодирования!

— Майк

P. S. Нравится, что я делаю? Подписывайтесь на меня!

SQL SERVER — как преобразовать набор результатов оператора Select в операторы Insert когда им необходимо перенести небольшой объем данных из одного экземпляра в другой или из одной среды в другую. Недавно я создал одну из новых баз данных филиалов моего клиента из базы данных других филиалов и столкнулся с этим сценарием. К счастью, со времен SQL Server 2005 у нас есть решение, но оно было очень сложным, особенно когда вам нужно было сделать это для таблиц, так как они имеют множество столбцов.

причина Я пишу это решение в том, что вы можете сделать это за несколько кликов в SQL Server 2012 и выше.
Прежде чем приступить к решению, я хотел бы создать образец для демонстрации решения.

Образец:
Ниже приведен оператор выбора, в котором я изменил набор результатов для демонстрации.

ИСПОЛЬЗОВАНИЕ [AdventureWorks2012]
ИДТИ
ВЫБЕРИТЕ [ИД отдела]
,[Имя] + 'Отдел' Как [Имя]
,[Имя группы]
,Getdate() как [ModifiedDate]
ОТ [Отдела кадров]. [Отдел]
ИДТИ

Ниже приведены два решения, одно из которых является традиционным, а другое можно использовать в SQL Server 2012 и более поздних версиях.

Решение 1. Использование конкатенации строк (традиционный метод)
В этом решении необходимо объединить результирующий набор оператора Select, чтобы преобразовать его в оператор Insert (с некоторыми изменениями данных). Вы должны убедиться, что одинарные кавычки (‘) находятся в правильных местах. Кроме того, если данные в результирующем наборе не относятся к строковому типу данных, вы должны преобразовать их в строковый тип данных для объединения. В случае, если в таблице есть столбец идентификаторов, вы должны передать имя столбца в операторе INSERT, а также с помощью SET IDENTITY_INSERT. Причина, по которой я НЕ  рекомендовать это решение, потому что если у вас больше столбцов в таблице, это также займет больше времени для разработки и отладки.

ИСПОЛЬЗОВАНИЕ [AdventureWorks2012]
ИДТИ
ВЫБИРАТЬ
'ВСТАВИТЬ В tbl_sample (
[DepartmentID],[Name],[GroupName],[ModifiedDate])
ЗНАЧЕНИЯ(' + ПРЕОБРАЗОВАТЬ(VARCHAR(50),[DepartmentID])
+ ',''' + [Имя] + 'Отдел'','
+ ''''+ [Имя группы] + ''','
+ ''''+ ПРЕОБРАЗОВАТЬ(VARCHAR(50),GETDATE(),120) + ''')'
ОТ [Отдела кадров]. [Отдел]
ИДТИ

Решение 2. Использование сценария создания (новый метод)
Этот метод применим к SQL Server 2012 и более поздним версиям и покажется вам довольно простым. Позвольте мне объяснить этот метод, используя два простых шага.

Шаг 1:
Прежде всего, вам нужно разработать оператор выбора, как это сделал я в образце, на основе ваших требований и ВСТАВИТЬ В ТАБЛИЦУ, как показано ниже.

ИСПОЛЬЗОВАНИЕ [AdventureWorks2012]
ИДТИ
ВЫБЕРИТЕ [ИД отдела]
,[Имя] + 'Отдел' Как [Имя]
,[Имя группы]
,Getdate() как [ModifiedDate]
INTO [tbl_Department_Sample] -- Результирующий набор вставлен в таблицу
ОТ [Отдела кадров].[Отдел]
ИДТИ

Шаг 2:
Набор результатов оператора select вставлен в таблицу ([tbl_Department_Sample]). Теперь вам просто нужно сгенерировать сценарий (только данные) таблицы ([tbl_Department_Sample]), используя функцию «Создать сценарий» в SQL Server 2012 и более поздних версиях.