Использование оператора SELECT в команде INSERT
Использование оператора SELECT в команде INSERT
Оператор SELECT в команде INSERT позволяет взять данные из одной или нескольких таблиц и вставить их в другую таблицу. Если вы вставляете значения только для части столбцов, определить значения для других столбцов можно будет позднее с помощью оператора UPDATE.
Если вы вставляете строки из одной таблицы в другую, эти таблицы должны иметь совместимую структуру, т.е. соответствующие столбцы должны иметь одинаковый тип, или же система должна уметь автоматически выполнять нужное преобразование.
Если столбцы в обеих таблицах совместимы по типам и определены в одинаковом порядке в соответствующих операторах CREATE TABLE, перечислять их в команде INSERT необязательно.
Пример
Предположим,
что в таблице sale_arhiiv содержатся строки с информацией о продажах в
том же
формате, что и в таблице tbl_sale.
Для добавления в таблицу sale_arhiiv строк из таблицы tbl_sale, содержащих данные о проджах за 2004 год в можно воспользоваться следующей командой:
SQL:
INSERT INTO sale_arhiiv
SELECT sale_id, client_id, prod_id, amount, saledate
FROM tbl_sale
WHERE YEAR(saledate)=2004
или
INSERT INTO sale_arhiiv
SELECT *
FROM tbl_sale
WHERE YEAR(saledate)=2004
Если столбцы в двух таблицах (таблица, в которую вы вставляете данные, и таблица, из которой вы берете данные) определены в разном порядке в соответствующих операторах CREATE TABLE, для установления соответствия между ними можно воспользоваться предложениями INSERT или SELECT.
Пример
Например,
предположим, что в операторе CREATE TABLE для таблицы tbl_sale
столбцы определены в следующем порядке — sale_id, client_id,
prod_id, amount, saledate, a
для
таблицы sale_arviiv — sale_id, prod_id, client_id saledate и
amount.
Тогда установить соответствие
между ними можно с помощью оператора INSERT. Для этого столбцы
таблицы sale_arhiiv нужно
перечислить в предложении INSERT:
SQL:
INSERT INTO sale_arhiiv
(sale_id, client_id, prod_id, amount, saledate)
SELECT *
FROM tbl_sale
WHERE YEAR(saledate)=2004
Такой же результат можно получить, перечислив в нужном порядке столбцы таблицы tbl_sale в предложении SELECT:
SQL:
INSERT INTO sale_arhiiv
SELECT sale_id,prod_id,
client_id,
saledate
amount
FROM tbl_sale
Одним
из преимуществ использования оператора SELECT в команде INSERT является
возможность включения в него различных выражений (expression)
— строк символов,
математических формул и функций, позволяющих манипулировать
вставляемыми
данными.
Кроме того, с помощью оператора SELECT можно добавлять данные как во все сразу, так и в отдельные столбцы, по аналогии с предложением VALUE. Для этого нужно просто задать имена столбцов, в которые вы хотите добавить данные в предложении INSERT
Пример
Ниже приводится пример предложения SELECT, в котором над столбцом выполняются математические действия.
Предположим, что описываемая нами компания купила продукцию у другой фармацевттической компании. Причем, по счастливой случайности, для описания продукции эта компания использовала таблицу tbl_prod с той же структурой, что и таблица tbl_product. Однако при покупке стоимость продукции была увеличена на 10%. Кроме того, данные будут добавлены во все поля таблицы tbl_product, кроме поля tbl_group. Оператор, увеличивающий значения стоимости товаров вставляющий строки из таблицы tbl_prod в таблицу tbl_product, имеет следующий вид:
SQL:
INSERT INTO tbl_product(prod_id, pr_name,price)
SELECT prod_id, name, price * 1.
1 FROM tbl_prod
« Previous | Next »
SQL INSERT INTO SELECT, заявление
❮ Предыдущая Следующая Глава ❯
С помощью SQL можно скопировать информацию из одной таблицы в другую.
INSERT INTO SELECT, заявление копирует данные из одной таблицы и вставляет его в существующую таблицу.
Заявление SQL INSERT INTO SELECT,
INSERT INTO SELECT, оператор выбирает данные из одной таблицы и вставляет его в существующую таблицу. Любые существующие строки в целевой таблице не изменяются.
SQL INSERT INTO SELECT, Синтаксис
Мы можем скопировать все столбцы из одной таблицы в другую, существующую таблицу:
INSERT INTO table2
SELECT * FROM table1;
Или же мы можем скопировать только те столбцы, которые мы хотим в другую, существующую таблицу:
(column_name(s))
SELECT column_name(s)
FROM table1;
Демо-версия базы данных
В этом уроке мы будем использовать хорошо известную базу данных Борей.
Ниже приводится подборка из «Customers» таблицы:
| Пользовательский ИД | Имя Клиента | Контактное лицо | Адрес | город | Почтовый индекс | Страна |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | 12209 | Germany | |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitucion 2222 | Mexico D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taqueria | Antonio Moreno | Mataderos 2312 | Mexico D.F. | 05023 | Mexico |
И выбор из «Suppliers» таблицы:
| SupplierID | Наименование поставщика | Контактное лицо | Адрес | город | Почтовый индекс | Страна | Телефон |
|---|---|---|---|---|---|---|---|
| 1 | Экзотические Liquid | Шарлотта Купер | 49 Гилберта St. | Londona | EC1 4SD | Великобритания | (171) 555-2222 |
| 2 | Новый Орлеан Cajun наслаждений | Shelley Берк | PO Box 78934 | Жители Нового Орлеана | 70117 | США | (100) 555-4822 |
| 3 | Homestead Бабушка Келли | Regina Мерфи | 707 Oxford Rd. | Ann Arbor | 48104 | США | (313) 555-5735 |
SQL INSERT INTO SELECT, Примеры
Копирование только несколько столбцов из «Suppliers» Into «Customers» :
пример
INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers;
Попробуй сам «
Копирование только немецких поставщиков в «Customers» :
пример
INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers
WHERE Country=’Germany’;
Попробуй сам «
❮ Предыдущая Следующая Глава ❯
sql — Вставить в .
.. значения ( SELECT … FROM … )Оба ответа, которые я вижу, отлично работают в Informix, и в основном являются стандартным SQL. То есть запись:
INSERT INTO target_table[()] SELECT ... FROM ...;
прекрасно работает с Informix и, как я ожидал, со всеми СУБД. (Когда-то 5 или более лет назад это было то, что MySQL не всегда поддерживал; теперь у него есть достойная поддержка такого стандартного синтаксиса SQL, и, насколько мне известно, он будет нормально работать с этой нотацией.) Список столбцов является необязательным, но указывает целевые столбцы в последовательности, поэтому первый столбец результата SELECT попадет в первый столбец в списке и т. д. При отсутствии списка столбцов первый столбец результата SELECT входит в список. первый столбец целевой таблицы.
Между системами может различаться нотация, используемая для идентификации таблиц в разных базах данных — стандарт ничего не говорит об операциях между базами данных (не говоря уже о межСУБД).
[dbase[@server]:][owner.]table
То есть вы можете указать базу данных, дополнительно указав сервер, на котором размещена эта база данных, если она не находится на текущем сервере, за которым следует необязательный владелец, точка и, наконец, фактическое имя таблицы. Стандарт SQL использует термин схема для того, что Informix называет владельцем. Таким образом, в Informix любая из следующих нотаций может идентифицировать таблицу:
таблица "владелец".таблица база данных: таблица база данных: владелец. таблица dbase@сервер: таблица dbase@сервер:владелец.таблица
Владелец вообще не нуждается в цитировании; однако, если вы используете кавычки, вам нужно правильно написать имя владельца — оно становится чувствительным к регистру. То есть:
кто-то.таблица "кто-то".таблица SOMEONE.table
все идентифицируют одну и ту же таблицу. С Informix есть небольшие сложности с базами данных MODE ANSI, где имена владельцев обычно преобразуются в верхний регистр (informix является исключением).
То есть в базе данных MODE ANSI (обычно не используемой) вы можете написать:
СОЗДАТЬ ТАБЛИЦУ Someone.table ( ... )
, а имя владельца в системном каталоге будет «Кто-то», а не «кто-то». Если вы заключите имя владельца в двойные кавычки, оно действует как идентификатор с разделителями. В стандартном SQL идентификаторы с разделителями можно использовать во многих местах. С Informix вы можете использовать их только вокруг имен владельцев — в других контекстах Informix обрабатывает как строки в одинарных, так и в двойных кавычках как строки, а не разделяет строки в одинарных кавычках как строки, а строки в двойных кавычках — как идентификаторы с разделителями. (Конечно, для полноты картины существует переменная окружения DELIMIDENT, которой можно присвоить любое значение, но безопаснее Y, чтобы указать, что двойные кавычки всегда окружают идентификаторы с разделителями, а одинарные кавычки всегда окружают строки.)
Обратите внимание, что MS SQL Server может использовать [идентификаторы с разделителями], заключенные в квадратные скобки.
Мне это кажется странным и уж точно не является частью стандарта SQL.
sql — INSERT — SELECT * вместо одного столбца
Хорошо, во-первых, ваш синтаксис не будет работать, несмотря ни на что. Попробуйте
INSERT INTO tableB (Field1, Field2, Field3, Field4, Field5, FieldX) ВЫБЕРИТЕ Field4, Field3, Field2, Field1, Field5, 'HARDCODEVALUE' КАК FieldX ИЗ таблицы А
Далее да, вы должны ВСЕГДА использовать все столбцы в том порядке, в котором вы хотите вставить их в таблицу как при выборе, так и при вставке. В противном случае это очень большой антипаттерн SQL, и это вызовет проблемы в будущем, особенно если столбцы меняются. Вы всегда хотите, чтобы правый столбец соответствовал правому столбцу. Это становится еще более проблематичным, если имена полей не всегда совпадают напрямую.
Если структура вашей таблицы меняется ежедневно, то у вас проблемы с дизайном, так как этого никогда не должно произойти.
Я мог бы предложить вам использовать таблицы информационной схемы для поиска столбцов на лету, но на самом деле, если таблица A добавляет столбец, как узнать, с каким столбцом его сопоставить в таблице B? Если столбец удален из таблицы B, как узнать, какой столбец удалить из таблицы A в SELECT? Позвольте мне проиллюстрировать проблему.
Предположим, у вас есть такая структура:
ТАБЛИЦА ID ПОЛЬЗОВАТЕЛЯ ЛОГИН ПОЛЬЗОВАТЕЛЯ ПОЛЬЗОВАТЕЛЬСКИЙ ПАРОЛЬ ТАБЛИЦАB ID ПОЛЬЗОВАТЕЛЯ СИСТЕМНЫЙ ВХОД СИСТЕМНЫЙ ПАРОЛЬ ТИП ПОЛЬЗОВАТЕЛЯ
Затем, глядя на SELECT * вставить
INSERT INTO tableB ВЫБЕРИТЕ *, 'Пользователь' КАК ПОЛЬЗОВАТЕЛЬСКИЙ ТИП ИЗ таблицы А
Эквивалент
INSERT INTO tableB (USERID,SYSLOGIN,SYSPASSWORD,USERTYPE) ВЫБЕРИТЕ ИДЕНТИФИКАТОР ПОЛЬЗОВАТЕЛЯ,ВХОД ПОЛЬЗОВАТЕЛЯ,ПАРОЛЬ ПОЛЬЗОВАТЕЛЯ, 'Пользователь' КАК ТИП ПОЛЬЗОВАТЕЛЯ ИЗ таблицы А
На данный момент все в порядке. Но что происходит, когда структура изменяется?
Затем структура становится
ТАБЛИЦА ID ПОЛЬЗОВАТЕЛЯ ЛОГИН ПОЛЬЗОВАТЕЛЯ ПОЛЬЗОВАТЕЛЬСКИЙ ПАРОЛЬ LASTLOGINDATE ТАБЛИЦАB ID ПОЛЬЗОВАТЕЛЯ СИСТЕМНЫЙ ВХОД СИСТЕМНЫЙ ПАРОЛЬ ТИП ПОЛЬЗОВАТЕЛЯ LASTLOGINDATE
Если вы используете select *
INSERT INTO tableB ВЫБИРАТЬ * ИЗ таблицы А
эквивалентен
INSERT INTO tableB (USERID,SYSLOGIN,SYSPASSWORD,USERTYPE, LASTLOGINDATE) ВЫБЕРИТЕ USERID,USERLOGIN,USERPASSWORD, LASTLOGINDATE,'User' AS USERTYPE ИЗ таблицы А
Как видите, используя SELECT *, вы в конечном итоге пытаетесь вставить LASTLOGINDATE в USERTYPE, а значение USERTYPE, которое вы ранее жестко запрограммировали, попадает в LASTLOGINDATE.