Универсальный 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 может быть подходящим инструментом для работы с большим объемом данных.