Insert (SQL)

редактировать

A SQL INSERT добавляет одну или несколько записей в любую единственную таблицу в реляционной базе данных.

Содержание

1 Базовая форма
2 Расширенные формы
- 2.1 Многострочная вставка
- 2.2 Копирование строк из других таблиц
- 2.3 Значения по умолчанию
3 Получение ключа
4 Триггеры
5 Ссылки
6 Внешние ссылки

Базовая форма

Операторы вставки имеют следующую форму:

INSERT INTO table (column1 [, column2, column3...]) VALUES (value1 [, value2, value3...])

Количество столбцов и значений должно быть одинаковым. Если столбец не указан, используется значение по умолчанию для столбца. Значения, указанные (или подразумеваемые) оператором INSERT, должны удовлетворять всем применимым ограничениям (таким как первичные ключи, ограничения CHECK и NOT NULL ограничения). Если возникает синтаксическая ошибка или если какие-либо ограничения нарушаются, новая строка не добавляется в таблицу и вместо этого возвращается ошибка.

Пример:

INSERT INTO phone_book (name, number) VALUES ('John Doe', '555-1212');

Также можно использовать сокращение, используя порядок столбцов при создании таблицы. Не требуется указывать все столбцы в таблице, поскольку любые другие столбцы будут принимать значения по умолчанию или останутся нулевым :

INSERT INTO table VALUES (value1, [value2,...])

Пример вставки данных в 2 столбца в таблице phone_book и игнорирования любых других столбцов, которые могут быть после первых 2 в таблице.

INSERT INTO phone_book VALUES ('John Doe', '555-1212');

Расширенные формы

Многострочные вставки

Функция SQL (начиная с SQL-92 ) - это использование конструкторов значений строк для вставки нескольких строк за раз в один оператор SQL:

INSERT INTO имя таблицы (столбец-a, [столбец-b,...]) VALUES ('значение-1а', ['значение-1b',...]), ('значение- 2a ', [' value-2b ',...]),...

Эта функция поддерживается DB2, SQL Server (начиная с версии 10.0 - т.е. 2008), PostgreSQL (начиная с версии 8.2), MySQL, SQLite (начиная с версии 3.7.11) и H2.

Пример (при условии, что 'name' и ' number '- единственные столбцы в таблице' phone_book '):

INSERT INTO phone_book VALUES (' John Doe ',' 555-1212 '), (' Peter Doe ',' 555-2323 ');

, который можно рассматривать как сокращение для двух операторов

INSERT INTO phone_book VALUES ('John Doe', '555-1212'); INSERT INTO phone_book VALUES ('Peter Doe', '555-2323');

Обратите внимание, что два отдельных оператора могут иметь разную семантику (особенно в отношении триггеров оператора ) и могут не обеспечивать такую же производительность, как одиночная многострочная вставка.

Чтобы вставить несколько строк в MS SQL, вы можете использовать такую конструкцию:

INSERT INTO phone_book SELECT 'John Doe', '555-1212' UNION ALL SELECT 'Peter Doe', '555-2323' ;

Обратите внимание, что это недопустимый оператор SQL в соответствии со стандартом SQL (SQL: 2003 ) из-за неполного предложения подзапроса.

Чтобы сделать то же самое в Oracle, используйте таблицу DUAL, которая всегда состоит только из одной строки:

INSERT INTO phone_book SELECT 'John Doe', '555-1212' FROM ДВОЙНОЕ СОЕДИНЕНИЕ ALL SELECT 'Peter Doe', '555-2323' FROM DUAL

Соответствующая стандарту реализация этой логики показывает следующий пример или как показано выше:

INSERT INTO phone_book SELECT 'John Doe', ' 555-1212 'FROM LATERAL (VALUES (1)) AS t (c) UNION ALL SELECT' Peter Doe ',' 555-2323 'FROM LATERAL (VALUES (1)) AS t (c)

Oracle PL / SQL поддерживает оператор INSERT ALL, где несколько операторов вставки завершаются SELECT:

INSERT ALL INTO phone_book VALUES ('John Doe', '555-1212') INTO phone_book VALUES ('Peter Doe', '555-2323') SELECT * FROM DUAL;

В Firebird вставка нескольких строк может быть достигнута следующим образом:

INSERT INTO phone_book ("name", "number") SELECT 'John Doe', '555-1212' FROM RDB $ DATABASE UNION ALL SELECT «Peter Doe», «555-2323» ИЗ RDB $ DATABASE;

Firebird, однако, ограничивает количество строк, которые могут быть вставлены таким образом, поскольку существует ограничение на количество контекстов, которые могут использоваться в одном запросе.

Копирование строк из других таблиц

Оператор INSERT также может использоваться для извлечения данных из других таблиц, изменения их при необходимости и вставки непосредственно в таблицу. Все это делается в одном операторе SQL, который не требует какой-либо промежуточной обработки в клиентском приложении. Вместо предложения VALUES используется подзапрос. Подзапрос может содержать соединения, вызовы функций и даже запрашивать ту же таблицу, в которую вставлены данные. Логически выбор оценивается до начала фактической операции вставки. Пример приведен ниже.

INSERT INTO phone_book2 SELECT * FROM phone_book WHERE name IN ('John Doe', 'Peter Doe')

Требуется вариант, когда некоторые данные из исходной таблицы вставляются в новую таблицу, но не вся запись. (Или когда таблицы схемы не совпадают.)

INSERT INTO phone_book2 ([name], [phoneNumber]) SELECT [name], [phoneNumber] FROM phone_book WHERE name IN ('John Doe ',' Peter Doe ')

Оператор SELECT создает (временную) таблицу, и схема этой временной таблицы должна совпадать со схемой таблицы, в которую вставляются данные.

Значения по умолчанию

Можно вставить новую строку без указания каких-либо данных, используя значения по умолчанию для всех столбцов. Однако некоторые базы данных отклоняют оператор, если данные не указаны, например Microsoft SQL Server, и в этом случае можно использовать ключевое слово DEFAULT.

INSERT INTO phone_book VALUES (DEFAULT)

Иногда базы данных также поддерживают альтернативный синтаксис для этого; например, MySQL позволяет опустить ключевое слово DEFAULT, а T-SQL может использовать DEFAULT VALUES вместо VALUES (DEFAULT). Ключевое слово DEFAULT также может использоваться при обычной вставке для явного заполнения столбца с использованием значения по умолчанию для этого столбца:

INSERT INTO phone_book VALUES (DEFAULT, '555-1212')

Что происходит, когда столбец не указать значение по умолчанию зависит от базы данных. Например, MySQL и SQLite заполнят пустое значение (кроме случаев, когда они находятся в строгом режиме), в то время как многие другие базы данных отклонят оператор.

Получение ключа

Разработчики баз данных, которые используют суррогатный ключ в качестве первичного ключа для каждой таблицы, будут время от времени сталкиваться с сценарием, когда им нужно автоматически извлекать базу данных. сгенерированный первичный ключ из оператора SQL INSERT для использования в других операторах SQL. Большинство систем не позволяют операторам SQL INSERT возвращать данные строки. Следовательно, в таких сценариях возникает необходимость в обходном пути. Общие реализации включают:

Использование специфической для базы данных хранимой процедуры, которая генерирует суррогатный ключ, выполняет операцию INSERT и, наконец, возвращает сгенерированный ключ. Например, в Microsoft SQL Server ключ извлекается с помощью специальной функции SCOPE_IDENTITY (), в то время как в SQLite функция называется last_insert_rowid().
с использованием специфической для базы данных оператор SELECT для временной таблицы, содержащей последнюю вставленную строку (строки). DB2 реализует эту функцию следующим образом:
```
SELECT * FROM NEW TABLE (INSERT INTO phone_book VALUES ('Peter Doe', '555-2323')) AS t
```
- DB2 for z / OS реализует эту функцию следующим образом.
```
ВЫБРАТЬ EMPNO, HIRETYPE, HIREDATE ИЗ ОКОНЧАТЕЛЬНОЙ ТАБЛИЦЫ (ВСТАВИТЬ В ЗНАЧЕНИЯ EMPSAMP (ИМЯ, ЗАРПЛАТА, ОТДЕЛ, УРОВЕНЬ) ('Мэри Смит', 35000.00, 11, 'Associate'));
```
Использование оператора SELECT после оператора INSERT со специальной функцией базы данных, которая возвращает сгенерированный первичный ключ для последней вставленной строки. Например, LAST_INSERT_ID () для MySQL.
Использование уникальной комбинации элементов из исходного SQL INSERT в последующем операторе SELECT.
Использование GUID в операторе SQL INSERT и извлечение его в операторе SELECT.
Использование предложения OUTPUT в операторе SQL INSERT для MS-SQL Server 2005 и MS-SQL Server 2008.
Использование оператора INSERT с предложением RETURNING для Oracle.
```
INSERT INTO phone_book VALUES ('Peter Doe', '555-2323') RETURNING phone_book_id INTO v_pb_id
```
Использование оператора INSERT с предложением RETURNING для PostgreSQL (начиная с версии 8.2). Возвращенный список идентичен результату INSERT.
- Firebird имеет такой же синтаксис в операторах языка модификации данных (DSQL); оператор может добавить не более одной строки. В хранимых процедурах, триггерах и исполнительных блоках (PSQL) используется вышеупомянутый синтаксис Oracle.
```
INSERT INTO phone_book VALUES ('Peter Doe', '555-2323') RETURNING phone_book_id
```
Использование IDENTITY () функция в H2 возвращает последний вставленный идентификатор.
```
ВЫБРАТЬ ИДЕНТИЧНОСТЬ ();
```

Триггеры

Если триггеры определены в таблице, с которой работает оператор INSERT, эти триггеры оцениваются в контексте операции. Триггеры BEFORE INSERT позволяют изменять значения, которые должны быть вставлены в таблицу. Триггеры AFTER INSERT больше не могут изменять данные, но могут использоваться для инициирования действий с другими таблицами, например, для реализации механизма аудита.

Ссылки

Внешние ссылки