Insert (SQL)

Правильный заголовок этой статьи — INSERT. Он показан некорректно из-за технических ограничений.

INSERT — оператор языка SQL, который позволяет добавить строки в таблицу, заполняя их значениями. Значения можно вставлять перечислением с помощью слова values и перечислив их в круглых скобках через запятую или оператором select.

Примеры использования

Используя перечисление значений, с указанием столбцов:

INSERT INTO <название таблицы> ([<Имя столбца>, ... ]) VALUES (<Значение>,...)

Используя перечисление значений, без указания столбцов:

не работает если использовать с set identity_insert

INSERT INTO <название таблицы> VALUES (<Значение>,...)

Используя select:

INSERT INTO <название таблицы> SELECT <имя столбца>,... FROM <название таблицы>

В последнем случае, в таблицу может вставиться более одной записи. Если в таблице есть другие поля требующие заполнения, но не указанные в операторе insert, для них будет установлено значение по умолчанию, либо null, если значение по умолчанию не указано.

Альтернативный синтаксис оператора INSERT

Значения присваиваются столбцам при помощи ключевого слова SET; не указанные в операторе столбцы принимают значение по умолчанию.

$query = mysql_query ("INSERT table SET id_user=1, name='Saveliy Severniy' ");

Особенности

Во время выполнения оператора могут возникнуть ошибки:

• если при создании таблицы для поля был указан параметр not null и не было определено значение по умолчанию (см. create), то при отсутствии для него вставляемого значения возникнет ошибка. Решение очевидно:
  • либо убрать параметр not null
  • либо указать значение по умолчанию
  • либо вставить значение
• если произойдет попытка вставки в поле с типом identity (автоинкремент), то также произойдет ошибка. Решить проблему можно двумя способами:
  • не вставлять значение в это поле
  • указать опцию identity_insert on после чего вставить уникальное значение для этого столбца

Опция identity_insert (MS SQL Server)

эта инструкция не будет работать, нужно указывать имя таблицы.

Включить опцию:

 SET IDENTITY_INSERT TABLE { ON | OFF }

После включения этой опции можно вставлять значения в поля определенные как identity. Нужно учесть, что значение должно быть уникальным.

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

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

Разработчики, которые используют суррогатный ключ в качестве первичного ключа, часто сталкиваются со сценарием, когда необходимо получить первичный ключ (для использования в других запросах), сгенерированный базой данных в ответ на оператор SQL INSERT. Но большинство систем не позволяют оператору 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 для z/OS реализует эту возможность следующим образом:

SELECT EMPNO, HIRETYPE, HIREDATE 
FROM FINAL TABLE (INSERT INTO EMPSAMP (NAME, SALARY, DEPTNO, LEVEL) VALUES(’Mary Smith’, 35000.00, 11, ’Associate’));

• Используя оператор SELECT после оператора INSERT, со специальной функцией, которая в данной БД возвращает сгенерированный первичный ключ для последней добавленной строки.
• Используя уникальную комбинацию полей, которые входили в исходный оператор SQL INSERT, в последующих вызовах оператора SELECT.
• Используя GUID в операторе SQL INSERT, получить результат через оператор SELECT.
• Используя функцию PHP mysql_insert_id() для MySQL после оператора INSERT.
• используя INSERT с последующим SELECT LAST_INSERT_ID() для MySQL.
• Используя оператор INSERT с предложением OUTPUT (Transact-SQL) начиная с Microsoft SQL Server 2005.

DECLARE @TABLE TABLE (
	[id] [SMALLINT] IDENTITY(1,1) NOT NULL,
	[name] [NCHAR](50) NULL);
 
INSERT INTO @TABLE
    OUTPUT INSERTED.id
VALUES ('Peter Doe');

• Используя оператор INSERT с модификатором RETURNING для PostgreSQL (с версии 8.2) и Firebird (с версии 2.0). Возвращаемый список идентичен результату оператора SELECT.

INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' )
RETURNING phone_book_id

• Используя оператор INSERT с модификатором RETURNING для Oracle. Такой же синтаксис используется в Firebird для процедур, триггеров и блоков исполнения.

INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' )
RETURNING phone_book_id INTO v_pb_id

• Функция IDENTITY() в H2 возвращает последний добавленный (ключ?)

SELECT IDENTITY();

См. также

• Инъекция SQL
• UPDATE
• DELETE