Вставить в... значения ( SELECT ... FROM ...)
Я пытаюсь INSERT INTO таблица с использованием входных данных из другой таблицы. Хотя это вполне осуществимо для многих механизмов баз данных, мне всегда кажется, что я не могу вспомнить правильный синтаксис для SQL двигатель дня ( MySQL, Oracle, SQL Server, Informix и DB2).
Существует ли синтаксис "серебряной пули" из стандарта SQL (например, SQL-92), который позволил бы мне вставлять значения, не беспокоясь о базовой базе данных?
28 ответов
Пытаться:
INSERT INTO table1 ( column1 )
SELECT col1
FROM table2
Это стандартный ANSI SQL и должен работать на любой СУБД
Это определенно работает для:
- оракул
- MS SQL Server
- MySQL
- Postgres
- SQLite v3
- Teradata
- DB2
- Sybase
- Vertica
- HSQLDB
- H2
- AWS RedShift
- SAP HANA
@ Shadow_x99: Это должно работать нормально, вы также можете иметь несколько столбцов и другие данные:
INSERT INTO table1 ( column1, column2, someInt, someVarChar )
SELECT table2.column1, table2.column2, 8, 'some string etc.'
FROM table2
WHERE table2.ID = 7;
Изменить: я должен отметить, что я использовал этот синтаксис только с Access, SQL 2000/2005/Express, MySQL и PostgreSQL, поэтому они должны быть рассмотрены. Комментатор указал, что он будет работать с SQLite3.
Чтобы получить только одно значение в множественном значении INSERT из другой таблицы я сделал следующее в SQLite3:
INSERT INTO column_1 ( val_1, val_from_other_table )
VALUES('val_1', (SELECT val_2 FROM table_2 WHERE val_2 = something))
Оба ответа, которые я вижу, отлично работают в Informix, и в основном это стандартный SQL. То есть обозначение:
INSERT INTO target_table[(<column-list>)] SELECT ... FROM ...;
отлично работает с Informix и, как я ожидаю, со всеми СУБД. (Когда-то 5 или более лет назад, MySQL не всегда поддерживал такие вещи; теперь он имеет приличную поддержку для такого рода стандартного синтаксиса SQL и, AFAIK, будет работать нормально в этой записи.) Список столбцов является необязательным, но указывает целевые столбцы в последовательности, поэтому первый столбец результата SELECT перейдет в первый перечисленный столбец и т. д. В отсутствие списка столбцов первый столбец результата SELECT перейдет в первый столбец целевой таблицы.
Что может отличаться в разных системах, так это нотация, используемая для идентификации таблиц в разных базах данных - стандарт не имеет ничего общего с операциями между базами данных (не говоря уже о базах данных). С Informix вы можете использовать следующие обозначения для идентификации таблицы:
[dbase[@server]:][owner.]table
То есть вы можете указать базу данных, опционально идентифицируя сервер, на котором размещена эта база данных, если он не находится на текущем сервере, за которым следует необязательный владелец, точка и, наконец, фактическое имя таблицы. Стандарт SQL использует термин схема для того, что Informix называет владельцем. Таким образом, в Informix любые из следующих обозначений могут идентифицировать таблицу:
table
"owner".table
dbase:table
dbase:owner.table
dbase@server:table
dbase@server:owner.table
Владелец вообще не должен быть указан; однако, если вы используете кавычки, вам нужно правильно написать имя владельца - оно становится чувствительным к регистру. То есть:
someone.table
"someone".table
SOMEONE.table
все идентифицируют одну и ту же таблицу. С Informix есть небольшое осложнение с базами данных MODE ANSI, где имена владельцев обычно преобразуются в верхний регистр (в качестве исключения используется informix). То есть в базе данных MODE ANSI (обычно не используется) вы можете написать:
CREATE TABLE someone.table ( ... )
и имя владельца в системном каталоге будет "НЕКОТОРЫЙ", а не "кто-то". Если вы заключите имя владельца в двойные кавычки, оно будет действовать как идентификатор с разделителями. В стандартном SQL идентификаторы с разделителями могут использоваться во многих местах. В Informix вы можете использовать их только вокруг имен владельцев - в других контекстах Informix обрабатывает как строки в одинарных кавычках, так и строки в двойных кавычках как строки, а не разделяет строки в одинарных кавычках как строки, а строки в двойных кавычках - как идентификаторы с разделителями. (Конечно, просто для полноты есть переменная окружения DELIMIDENT, которую можно установить на любое значение, но Y безопаснее - указать, что двойные кавычки всегда окружают идентификаторы с разделителями, а одинарные кавычки всегда окружают строки.)
Обратите внимание, что MS SQL Server удается использовать [идентификаторы с разделителями], заключенные в квадратные скобки. Это выглядит странно для меня, и, конечно, не является частью стандарта SQL.
Два подхода для вставки с подзапросом select.
- Подзапрос SELECT возвращает результаты с одной строкой.
- Подзапрос SELECT возвращает результаты с несколькими строками.
1. Подход с подзапросом With SELECT, возвращающий результаты с одной строкой.
INSERT INTO <table_name> (<field1>, <field2>, <field3>)
VALUES ('DUMMY1', (SELECT <field> FROM <table_name> ),'DUMMY2');
В этом случае предполагается, что подзапрос SELECT возвращает только одну строку результата на основе условия WHERE или агрегатных функций SQL, таких как SUM, MAX, AVG и т. Д. В противном случае будет выдана ошибка.
2. Подход с подзапросом With SELECT, возвращающим результаты с несколькими строками.
INSERT INTO <table_name> (<field1>, <field2>, <field3>)
SELECT 'DUMMY1', <field>, 'DUMMY2' FROM <table_name>;
Второй подход будет работать в обоих случаях.
Чтобы добавить что-то в первый ответ, когда нам нужно всего несколько записей из другой таблицы (в этом примере только одна):
INSERT INTO TABLE1
(COLUMN1, COLUMN2, COLUMN3, COLUMN4)
VALUES (value1, value2,
(SELECT COLUMN_TABLE2
FROM TABLE2
WHERE COLUMN_TABLE2 like "blabla"),
value4);
Вместо VALUES часть INSERT запрос, просто используйте SELECT запрос, как показано ниже.
INSERT INTO table1 ( column1 , 2, 3... )
SELECT col1, 2, 3... FROM table2
Большинство баз данных придерживаются основного синтаксиса,
INSERT INTO TABLE_NAME
SELECT COL1, COL2 ...
FROM TABLE_YOU_NEED_TO_TAKE_FROM
;
Каждая база данных, которую я использовал, имеет следующий синтаксис: DB2, SQL Server, MY SQL, PostgresQL
Это можно сделать без указания столбцов в INSERT INTO часть, если вы предоставляете значения для всех столбцов в SELECT часть.
Допустим, таблица 1 имеет два столбца. Этот запрос должен работать:
INSERT INTO table1
SELECT col1, col2
FROM table2
Это не будет работать (значение для col2 не указано):
INSERT INTO table1
SELECT col1
FROM table2
Я использую MS SQL Server. Я не знаю, как работают другие RDMS.
Просто используйте круглые скобки для предложения SELECT в INSERT. Например, вот так:
INSERT INTO Table1 (col1, col2, your_desired_value_from_select_clause, col3)
VALUES (
'col1_value',
'col2_value',
(SELECT col_Table2 FROM Table2 WHERE IdTable2 = 'your_satisfied_value_for_col_Table2_selected'),
'col3_value'
);
Это еще один пример использования значений с помощью select:
INSERT INTO table1(desc, id, email)
SELECT "Hello World", 3, email FROM table2 WHERE ...
Простая вставка, когда известна последовательность столбцов таблицы:
Insert into Table1
values(1,2,...)
Простая вставка с упоминанием колонки:
Insert into Table1(col2,col4)
values(1,2)
Массовая вставка, когда количество выбранных столбцов таблицы (#table2) равно таблице вставки (Table1)
Insert into Table1 {Column sequence}
Select * -- column sequence should be same.
from #table2
Массовая вставка, если вы хотите вставить только в нужный столбец таблицы (таблица1):
Insert into Table1 (Column1,Column2 ....Desired Column from Table1)
Select Column1,Column2..desired column from #table2
from #table2
Вот как вставить из нескольких таблиц. В этом конкретном примере у вас есть таблица сопоставления в сценарии "многие ко многим":
insert into StudentCourseMap (StudentId, CourseId)
SELECT Student.Id, Course.Id FROM Student, Course
WHERE Student.Name = 'Paddy Murphy' AND Course.Name = 'Basket weaving for beginners'
(Я понимаю, что сопоставление имени ученика может возвращать более одного значения, но вы понимаете, что сопоставление с чем-то другим, кроме идентификатора, необходимо, когда идентификатор является столбцом идентификатора и неизвестен.)
Вот еще один пример, где источник взят более чем из одной таблицы:
INSERT INTO cesc_pf_stmt_ext_wrk(
PF_EMP_CODE ,
PF_DEPT_CODE ,
PF_SEC_CODE ,
PF_PROL_NO ,
PF_FM_SEQ ,
PF_SEQ_NO ,
PF_SEP_TAG ,
PF_SOURCE)
SELECT
PFl_EMP_CODE ,
PFl_DEPT_CODE ,
PFl_SEC ,
PFl_PROL_NO ,
PF_FM_SEQ ,
PF_SEQ_NO ,
PFl_SEP_TAG ,
PF_SOURCE
FROM cesc_pf_stmt_ext,
cesc_pfl_emp_master
WHERE pfl_sep_tag LIKE '0'
AND pfl_emp_code=pf_emp_code(+);
COMMIT;
Вы можете попробовать это, если вы хотите вставить все столбцы, используя SELECT * INTO Таблица.
SELECT *
INTO Table2
FROM Table1;
Я на самом деле предпочитаю следующее в SQL Server 2008:
SELECT Table1.Column1, Table1.Column2, Table2.Column1, Table2.Column2, 'Some String' AS SomeString, 8 AS SomeInt
INTO Table3
FROM Table1 INNER JOIN Table2 ON Table1.Column1 = Table2.Column3
Это исключает этап добавления набора Insert (), и вы просто выбираете, какие значения идут в таблице.
Это сработало для меня:
insert into table1 select * from table2
Предложение немного отличается от предложения Oracle.
INSERT INTO yourtable
SELECT fielda, fieldb, fieldc
FROM donortable;
Это работает на всех СУБД
Для Microsoft SQL Server я рекомендую научиться интерпретировать SYNTAX, предоставляемый в MSDN. С Google проще, чем когда-либо, искать синтаксис.
Для этого конкретного случая попробуйте
Google: вставить сайт:microsoft.com
Первый результат будет http://msdn.microsoft.com/en-us/library/ms174335.aspx
прокрутите вниз до примера ("Использование параметров SELECT и EXECUTE для вставки данных из других таблиц"), если вам трудно интерпретировать синтаксис, приведенный в верхней части страницы.
[ WITH <common_table_expression> [ ,...n ] ]
INSERT
{
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ]
{ <object> | rowset_function_limited
[ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
}
{
[ ( column_list ) ]
[ <OUTPUT Clause> ]
{ VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n ]
| derived_table <<<<------- Look here ------------------------
| execute_statement <<<<------- Look here ------------------------
| <dml_table_source> <<<<------- Look here ------------------------
| DEFAULT VALUES
}
}
}
[;]
Это должно быть применимо к любой другой СУБД, доступной там. Нет смысла запоминать весь синтаксис для всех продуктов IMO.
INSERT INTO FIRST_TABLE_NAME (COLUMN_NAME)
SELECT COLUMN_NAME
FROM ANOTHER_TABLE_NAME
WHERE CONDITION;
Лучший способ вставить несколько записей из любых других таблиц.
INSERT INTO dbo.Users
( UserID ,
Full_Name ,
Login_Name ,
Password
)
SELECT UserID ,
Full_Name ,
Login_Name ,
Password
FROM Users_Table
(INNER JOIN / LEFT JOIN ...)
(WHERE CONDITION...)
(OTHER CLAUSE)
select *
into tmp
from orders
Выглядит красиво, но работает, только если tmp не существует (создает и заполняет). (SQL-сервер)
Чтобы вставить в существующую таблицу tmp:
set identity_insert tmp on
insert tmp
([OrderID]
,[CustomerID]
,[EmployeeID]
,[OrderDate]
,[RequiredDate]
,[ShippedDate]
,[ShipVia]
,[Freight]
,[ShipName]
,[ShipAddress]
,[ShipCity]
,[ShipRegion]
,[ShipPostalCode]
,[ShipCountry] )
select * from orders
set identity_insert tmp off
ЕСЛИ вы хотите вставить некоторые данные в таблицу, но не хотите писать имя столбца.
INSERT INTO CUSTOMER_INFO
(SELECT CUSTOMER_NAME,
MOBILE_NO,
ADDRESS
FROM OWNER_INFO cm)
Где находятся таблицы:
CUSTOMER_INFO || OWNER_INFO
----------------------------------------||-------------------------------------
CUSTOMER_NAME | MOBILE_NO | ADDRESS || CUSTOMER_NAME | MOBILE_NO | ADDRESS
--------------|-----------|--------- || --------------|-----------|---------
A | +1 | DC || B | +55 | RR
Результат:
CUSTOMER_INFO || OWNER_INFO
----------------------------------------||-------------------------------------
CUSTOMER_NAME | MOBILE_NO | ADDRESS || CUSTOMER_NAME | MOBILE_NO | ADDRESS
--------------|-----------|--------- || --------------|-----------|---------
A | +1 | DC || B | +55 | RR
B | +55 | RR ||
Если вы идете по маршруту INSERT VALUES для вставки нескольких строк, обязательно разделите VALUES на наборы, используя скобки, поэтому:
INSERT INTO `receiving_table`
(id,
first_name,
last_name)
VALUES
(1002,'Charles','Babbage'),
(1003,'George', 'Boole'),
(1001,'Donald','Chamberlin'),
(1004,'Alan','Turing'),
(1005,'My','Widenius');
В противном случае MySQL возражает, что "Количество столбцов не соответствует количеству значений в строке 1", и вы в конечном итоге пишете тривиальный пост, когда, наконец, выясняете, что с этим делать.
Если вы сначала создаете таблицу, вы можете использовать ее так:
select * INTO TableYedek From Table
Этот метод вставляет значения, но по-другому при создании новой таблицы копирования.
В informix это работает, как сказал Клод:
INSERT INTO table (column1, column2)
VALUES (value1, value2);
Postgres поддерживает следующее: создать таблицу company.monitor2 как select * from company.monitor;
INSERT INTO Table_Name (COL1, COL2, COL3, ...)
VALUES (Value1, Value2, Value3, ...);
С именем схемы (как в PostgreSQL):
INSERT INTO "Schema-name"."Table_Name" (COL1, COL2, COL3, ...)
VALUES (Value1, Value2, Value3, ...);