Вставить (SQL) - Insert (SQL)

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

Основная форма

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

ВСТАВИТЬ В таблицу ( столбец1 [, столбец2 , столбец3 ...]) ЗНАЧЕНИЯ ( значение1 [, значение2 , значение3 ...])

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

Пример:

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

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

ВСТАВИТЬ В таблицу ЗНАЧЕНИЯ ( значение1 ; [ значение2 ; ...])

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

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

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

Многорядные пластины

Особенностью SQL (начиная с SQL-92 ) является использование конструкторов значений строк для вставки нескольких строк за раз в одном операторе SQL:

INSERT INTO tablename (column-a, [column-b, ...])
VALUES ('value-1a', ['value-1b', ...]),
       ('value-2a', ['value-2b', ...]),
       ...

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

Пример (предполагается, что "имя" и "номер" - единственные столбцы в таблице "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 DUAL
UNION 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' FROM 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, number)
SELECT name, number
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 ключевым слово также может быть использовано в обычной вставке явно заполнить столбец , используя значение этого столбца по умолчанию:

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 для 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 с функцией, зависящей от базы данных, которая возвращает сгенерированный первичный ключ для последней вставленной строки. Например, 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 возвращает последний вставленный идентификатор.
    SELECT IDENTITY();
    

Триггеры

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

использованная литература

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