Руководство по стандартному синтаксису SQL INSERT INTO SELECT

Стандартный синтаксис SQL для вставки значений из одной таблицы в другой, который работает в разных системах управления базами данных, — это INSERT INTO target_table[(column_list)] SELECT ... FROM ...;. Этот синтаксис, соответствующий стандарту SQL-92, поддерживается MySQL, Oracle, SQL Server, Informix и DB2 с минимальными или без каких-либо изменений, что делает его наиболее переносимым решением для кросс-базовых операций. Необязательный список столбцов позволяет указать, какие столбцы заполнять, что делает его гибким для различных сценариев миграции данных.

Содержание

• Базовый синтаксис INSERT INTO SELECT
• Указание списка столбцов
• Кросс-базовая совместимость
• Практические примеры
• Лучшие практики

Базовый синтаксис INSERT INTO SELECT

Фундаментальный синтаксис SQL-92 для вставки данных из одной таблицы в другую имеет следующую структуру:

INSERT INTO target_table
SELECT column1, column2, ...
FROM source_table
WHERE condition;

Как указано на Stack Overflow, “Это поддерживается в SQL-92 и работает с минимальными или без изменений в MySQL, PostgreSQL, SQL Server, Oracle и DB2.”

Базовый синтаксис включает:

• INSERT INTO target_table - указывает целевую таблицу
• SELECT column1, column2, ... - выбирает столбцы из исходной таблицы
• FROM source_table - указывает исходную таблицу
• WHERE condition - необязательное условие фильтрации

Этот синтаксис является частью стандарта SQL-92, который был “крупной редакцией стандарта”, введшей многие функции, все еще используемые сегодня Wikipedia.

Указание списка столбцов

Когда нужно указать, какие столбцы заполнять в целевой таблице, можно включить список столбцов:

INSERT INTO target_table (column1, column2, column3)
SELECT source_col1, source_col2, source_col3
FROM source_table;

Как отмечено в DevTip, “Оба ответа, которые я вижу, работают нормально в Informix, и в основном это стандартный SQL. То есть, обозначение: INSERT INTO target_table[(<column-list>)] SELECT ... FROM ...; работает нормально с Informix, и я ожидаю, что со всеми СУБД.”

Указание списка столбцов позволяет:

• Выбирать только определенные столбцы из исходной таблицы
• Сопоставлять столбцы с разными типами данных или позициями
• Пропускать столбцы, которые должны сохранять значения по умолчанию
• Обрабатывать случаи, когда исходные и целевые таблицы имеют разную структуру

Кросс-базовая совместимость

Синтаксис INSERT INTO SELECT удивительно последователен across основных систем управления базами данных, что делает его отличным выбором для кросс-базовых приложений.

Матрица совместимости

Как объясняет Tech Champion, “Основная проблема заключается в запоминании точного синтаксиса, который различается между разными системами управления базами данных SQL, такими как MySQL, Oracle, SQL Server, Informix и DB2.” Однако стандартный синтаксис INSERT INTO SELECT остается последовательным across этих платформ.

Практические примеры

Вот несколько практических примеров, демонстрирующих синтаксис INSERT INTO SELECT в действии:

Пример 1: Базовое копирование данных

-- Копируем все данные из employees в employees_archive
INSERT INTO employees_archive
SELECT * FROM employees;

Пример 2: Список столбцов с преобразованием данных

-- Копируем определенные столбцы с преобразованием данных
INSERT INTO customer_summary (customer_id, full_name, registration_date)
SELECT 
    customer_id,
    CONCAT(first_name, ' ', last_name) AS full_name,
    CURRENT_TIMESTAMP AS registration_date
FROM customers
WHERE status = 'active';

Пример 3: Условная вставка данных

-- Вставляем только записи, удовлетворяющие определенным условиям
INSERT INTO high_value_customers (customer_id, total_purchases, customer_name)
SELECT 
    c.customer_id,
    SUM(o.total_amount) AS total_purchases,
    c.customer_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING SUM(o.total_amount) > 10000;

Пример 4: Создание кросс-базовой таблицы

-- Создаем новую таблицу, копируя данные из другой таблицы
INSERT INTO new_table (id, name, created_at)
SELECT 
    id,
    name,
    CURRENT_TIMESTAMP
FROM existing_table
WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';

Эти примеры демонстрируют, как стандартный синтаксис SQL INSERT INTO SELECT можно использовать для различных сценариев миграции и преобразования данных, сохраняя совместимость across разных систем управления базами данных.

Лучшие практики

При использовании синтаксиса INSERT INTO SELECT across разных систем управления базами данных, учитывайте следующие лучшие практики:

1. Всегда используйте списки столбцов

Явно указывайте списки столбцов вместо использования SELECT * для обеспечения совместимости и предотвращения проблем при изменении структуры таблиц.

-- Хорошая практика
INSERT INTO target_table (col1, col2, col3)
SELECT source_col1, source_col2, source_col3
FROM source_table;

-- Избегайте (может вызвать проблемы)
INSERT INTO target_table
SELECT * FROM source_table;

2. Обрабатывайте совместимость типов данных

Убедитесь, что исходные и целевые столбцы имеют совместимые типы данных, чтобы избежать ошибок преобразования.

3. Используйте блоки транзакций

Оборачивайте операции INSERT INTO SELECT в транзакции для обеспечения целостности данных:

BEGIN TRANSACTION;
INSERT INTO target_table
SELECT * FROM source_table WHERE condition;
COMMIT TRANSACTION;

4. Учитывайте производительность для больших наборов данных

Для больших миграций данных рассмотрите:

• Использование пакетной обработки с предложениями LIMIT или TOP
• Добавление соответствующих индексов к исходным и целевым таблицам
• Выполнение операций в периоды низкой нагрузки

5. Тестируйте сначала в разработке

Всегда тестируйте операторы INSERT INTO SELECT в средах разработки или тестирования перед выполнением на производственных данных.

Как указано в документации MySQL, “Оператор INSERT вставляет одну или несколько строк в существующую таблицу. Вы можете указать значения явно или выбрать их из другой таблицы.”

Заключение

Стандартный синтаксис SQL-92 INSERT INTO target_table[(column_list)] SELECT ... FROM ...; предоставляет универсальное решение для вставки данных из одной таблицы в другую across разных систем управления базами данных. Этот синтаксис последовательно работает с MySQL, Oracle, SQL Server, Informix и DB2, что делает его идеальным для кросс-базовых приложений и проектов миграции данных.

Ключевые выводы включают:

• Используйте явные списки столбцов для лучшей совместимости и поддерживаемости
• Синтаксис соответствует стандарту SQL-92 и работает across основных систем управления базами данных
• Всегда тестируйте свои операторы и учитывайте производительность
• Используйте транзакции для обеспечения целостности данных во время больших операций

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

Источники

1. Stack Overflow - Insert into … values ( SELECT … FROM … )
2. W3Schools - SQL INSERT INTO SELECT Statement
3. DevTip - Insert into … values ( SELECT … FROM … )
4. Tech Champion - Comprehensive Guide on INSERT INTO Using SELECT
5. DB2 Tutorial - Db2 INSERT INTO SELECT By Practical Examples
6. Wikipedia - SQL-92
7. SQLines - MySQL INSERT Guide