Универсальный INSERT INTO SELECT в SQL: MySQL, Oracle

Быстрый ответ

Для переноса данных из одной таблицы в другую воспользуйтесь следующим шаблоном SQL:

INSERT INTO целевая_таблица (целевой_столбец1, целевой_столбец2, ...)
SELECT исходный_столбец1, исходный_столбец2, ...
FROM исходная_таблица
WHERE условие;

В данном слуае данные из исходная_таблица, отвечающие определенному условию, легко перенести в целевая_таблица. Успешное выполнение операции обеспечивается соответствующими типами данных и отсутствием конфликтов с уникальными ключами или ограничениями.

Рассмотрим пример, когда кандидат принимается на работу:

INSERT INTO сотрудники (имя, отдел)
SELECT имя_кандидата, желаемый_отдел
FROM кандидаты
WHERE прошли_собеседование AND приняты;

При необходимости вставить данные только в одну строку, используйте SELECT в комбинации с VALUES:

INSERT INTO продукты (название, цена)
VALUES ((SELECT название FROM ожидаемые_продукты WHERE id = 1), (SELECT цена FROM ожидаемые_продукты WHERE id = 1));

Убедитесь, что подзапрос возвращает только один результат.

Работа с данными и выбор столбцов

Вставка отдельных строк с уникальными значениями

Для добавления статических значений вместе с результатами подзапроса:

INSERT INTO отчеты (дата, обобщение)
VALUES (CURRENT_DATE, (SELECT COUNT(*) FROM продажи WHERE дата_продажи = CURRENT_DATE));

Здесь CURRENT_DATE является константой, а подзапрос подсчитывает количество совершенных сегодня продаж.

Подробнее об этом расскажет наш спикер на видео

Выбор столбцов и особенности идентификаторов

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

-- Многие мечтают о работе в инженерной команде!
INSERT INTO команда (id, имя, роль)
SELECT идентификатор_сотрудника, имя_сотрудника, 'Член Отряда'
FROM персонал
WHERE отдел = 'Инженерия';

В каждой СУБД используются свои типы идентификаторов — MS SQL Server использует [ ], а Informix — ".

Работа со схемами

При осуществлении вставки необходимо учитывать схему (или "владельца" в терминах Informix), это особенно важно в ANSI-режиме.

Агрегатные функции – наши помощники в формировании отчетов

Агрегатные функции добавляют дополнительную пользу, помогая создать сводные данные:

-- Давайте изучим продажи за каждый месяц!
INSERT INTO сводка_продаж_по_месяцам (месяц, всего_продаж)
SELECT EXTRACT(MONTH FROM дата_продажи), SUM(сумма)
FROM продажи
WHERE EXTRACT(YEAR FROM дата_продажи) = 2023
GROUP BY EXTRACT(MONTH FROM дата_продажи);

Вставка нескольких констант и строк

Для добавления различных значений отдельными строками используйте:

-- Расскажем о ряде интересных событий!
INSERT INTO уведомления (сообщение, дата_отправки)
SELECT 'Ознакомьтесь с новыми функциями!', CURRENT_DATE
FROM generate_series(1, 10);

Для добавления нескольких строк не требуется использовать VALUES:

-- "Хьюстон, мы приступаем к созданию резервной копии!
INSERT INTO резервная_таблица
SELECT * FROM основная_таблица
WHERE последнее_обновление < CURRENT_TIMESTAMP – INTERVAL '1 month';

Визуализация

Действие INSERT INTO ... SELECT FROM можно представить как доставку еды: ресторан представляет собой исходная_таблица, грузовик доставки – это INSERT INTO, а дом – это целевая_таблица.

Дома (🏠): [Дом А, Дом Б, Дом В]
Заказы на еду (🍝🍜🌯): [Пицца, Рамен, Буррито]

Доставка осуществляется так:

-- Тук-тук! Ваш ужин готов!
INSERT INTO Дома (Ужин)
SELECT Блюда FROM Заказы;

И после доставки:

🏠: [Дом А(Пицца), Дом Б(Рамен), Дом В(Буррито)]

Это просто, наглядно и понятно!

Решение проблем и оговорки

Работа с дубликатами и конфликтами

В случае появления дубликатов или конфликтов используйте такие инструменты, как ON CONFLICT в PostgreSQL или ON DUPLICATE KEY UPDATE в MySQL.

Условные вставки

Если нужно добавить данные только при выполнении определенных условий, примените к SELECT оператор WHERE, либо используйте конструкции CASE внутри SELECT.

Использование разделенных таблиц

При работе с разделенными таблицами обязательно убедитесь, что INSERT INTO сконцентрирован на нужном разделе, если ваша СУБД предусматривает такую возможность.

Блокировки и производительность

При массовых вставках необходимо контролировать блокировки и производительность, настраивая уровни изоляции транзакций или проводя вставку по частям. BULK INSERT в SQL Server может быть подходящим инструментом для работы с большим объемом данных.

Полезные материалы

1. sql – Вставка из ... значений (SELECT ... FROM ...) – Stack Overflow
2. Обзор оператора INSERT INTO SELECT и примеры его использования
3. INSERT (Transact-SQL) – SQL Server | Microsoft Learn
4. Оператор SQL INSERT INTO SELECT
5. INSERT
6. PostgreSQL: Документация: 16: INSERT