Insert (SQL)

Insert (SQL)

An SQL INSERT statement adds one or more records to any single table in a relational database.

Basic form

Insert statements have the following form:
* INSERT INTO "table" ("column1", ["column2, ... "] ) VALUES ("value1", ["value2, ..."] )

The number of columns and values must be the same. If a column is not specified, the default value for the column is used. The values specified (or implied) by the INSERT statement must satisfy all the applicable constraints (such as primary keys, CHECK constraints, and NOT NULL constraints). If a syntax error occurs or if any constraints are violated, the new row is not added to the table and an error returned instead.

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

When values for all columns in the table are specified, then a shorthand may be used, taking advantage of the order of the columns when the table was created:

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

Example (assuming that 'name' and 'number' are the only columns in the 'phone_book' table): INSERT INTO phone_book VALUES ('John Doe', '555-1212');

Advanced forms

Multirow inserts

An SQL feature (since SQL-92) is the use of "row value constructors" to insert multiple rows at a time in a single SQL statement:

INSERT INTO "table" ("column1", ["column2, ... "] ) VALUES ("value1a", ["value1b, ..."] ), ("value2a", ["value2b, ..."] ), ...

This feature is supported by DB2, PostgreSQL (since version 8.2), MySQL, and H2.

Example (assuming that 'name' and 'number' are the only columns in the 'phone_book' table): INSERT INTO phone_book VALUES ('John Doe', '555-1212'), ('Peter Doe', '555-2323');which may be seen as a shorthand for the two statements INSERT INTO phone_book VALUES ('John Doe', '555-1212'); INSERT INTO phone_book VALUES ('Peter Doe', '555-2323');Note that the two separate statements may have different semantics (especially with respect to statement triggers) and may not provide the same performance as a single multi-row insert.

To insert multiple rows in MS SQL you can use such a construction: INSERT INTO phone_book SELECT 'John Doe', '555-1212' UNION ALL SELECT 'Peter Doe', '555-2323';Note that this is not a valid SQL statement according to the SQL standard () due to the incomplete subselect clause.

To do the same in Oracle use the DUAL table, which always consists of a single row only: INSERT INTO phone_book SELECT 'John Doe', '555-1212' FROM DUAL UNION ALL SELECT 'Peter Doe','555-2323' FROM DUALA standard-conforming implementation of this logic shows the following example, or as shown above: 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)

Copying rows from other tables

An INSERT statement can also be used to retrieve data from other, modify it if necessary and insert it directly into the table. All this is done in a single SQL statement that does not involve any intermediary processing in the client application. A subselect is used instead of the VALUES clause. The subselect can contain joins, function calls, and it can even query the same table into which the data is inserted. Logically, the select is evaluated before the actual insert operation is started. An example is given below. INSERT INTO phone_book2 SELECT * FROM phone_book WHERE name IN ('John Doe', 'Peter Doe')A variation is needed when some of the data from the source table is being inserted into the new table, but not the whole record. (Or when the tables' schemas are not the same.) INSERT INTO phone_book2 ( [name] , [phoneNumber] ) SELECT [name] , [phoneNumber] FROM phone_book WHERE name IN ('John Doe', 'Peter Doe')The SELECT statement produces a (temporary) table, and the schema of that temporary table must match with the schema of the table where the data is inserted into.

Retrieving the key

Database designers that use a surrogate key as the primary key for every table will run into the occasional scenario where they need to automatically retrieve the database generated primary key from a SQL INSERT statement for use in another SQL statements. Most systems do not allow SQL INSERT statements to return row data. Therefore, it becomes necessary to implement a workaround in such scenarios. Common implementations include:

* Using a database-specific stored procedure that generates the surrogate key, performs the INSERT operation, and finally returns the generated key. For example, in Microsoft SQL Server, the key is retrieved via the SCOPE_IDENTITY() special function.
* Using a database-specific SELECT statement on a temporary table containing last inserted row(s). DB2 implements this feature in the following way: SELECT * FROM NEW TABLE ( INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' ) ) AS tDB2 for z/OS for z/OS implements this feature in the following way. SELECT EMPNO, HIRETYPE, HIREDATE FROM FINAL TABLE (INSERT INTO EMPSAMP (NAME, SALARY, DEPTNO, LEVEL) VALUES(’Mary Smith’, 35000.00, 11, ’Associate’));
* Using a SELECT statement after the INSERT statement with a database-specific function that returns the generated primary key for the most recently inserted row.
* Using a unique combination of elements from the original SQL INSERT in a subsequent SELECT statement.
* Using a GUID in the SQL INSERT statement and retrieving it in a SELECT statement.
* Using the PHP function mysql_insert_id() for MySQL after the INSERT statement.
* Using an INSERT statement with RETURNING clause for Oracle, which can only be used within a PL/SQL block INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' ) RETURNING phone_book_id INTO v_pb_id
* Using the IDENTITY() function in H2 returns the last identity inserted. SELECT IDENTITY();

Triggers

If triggers are defined on the table on which the INSERT statement operates, those triggers are evaluated in the context of the operation. BEFORE INSERT triggers allow the modification of the values that shall be inserted into the table. AFTER INSERT triggers cannot modify the data anymore, but can be used to initiate actions on other tables, for example to implement auditing mechanisms.

References

ee also


Wikimedia Foundation. 2010.

Игры ⚽ Поможем сделать НИР

Look at other dictionaries:

  • Insert (SQL) — У этого термина существуют и другие значения, см. Insert. Правильный заголовок этой статьи  INSERT. Он показан некорректно из за технических ограничений. INSERT  оператор языка SQL, который позволяет добавить строки в таблицу, заполняя… …   Википедия

  • Insert — may refer to: *Insert (advertising) *Insert (effects processing) *Insert (film) *Insert key on a computer keyboard, used to switch between insert mode and overstrike mode *Insert (molecular biology) *Insert (SQL) *Another name for a tipped tool,… …   Wikipedia

  • SQL — Desarrollador(es) IBM ISO/IEC 9075 1:2008 Información general Paradigma Multiparadigma …   Wikipedia Español

  • SQL-92 — SQL (das Kürzel für Structured Query Language; offizielle Aussprache [ɛskjuːˈɛl], häufig auch [ˈsiːkwəl] →SEQUEL), ist eine Datenbanksprache zur Definition, Abfrage und Manipulation von Daten in relationalen Datenbanken. SQL ist von ANSI und ISO… …   Deutsch Wikipedia

  • SQL-99 — SQL (das Kürzel für Structured Query Language; offizielle Aussprache [ɛskjuːˈɛl], häufig auch [ˈsiːkwəl] →SEQUEL), ist eine Datenbanksprache zur Definition, Abfrage und Manipulation von Daten in relationalen Datenbanken. SQL ist von ANSI und ISO… …   Deutsch Wikipedia

  • SQL — Класс языка: Мультипарадигмальный Появился в: 1974 Автор(ы): Дональд Чэмбэрлин Рэймонд Бойс Релиз: SQL:2008 (2008) Типизация данных …   Википедия

  • SQL Server Compact — Saltar a navegación, búsqueda Microsoft SQL Server Compact (SQL Server CE) es un motor de base de datos relacional, de libre descarga y distribución, tanto para dispositivos móviles como para aplicaciones escritorio. Especialmente orientada a… …   Wikipedia Español

  • SQL-Injection — (dt. SQL Einschleusung) bezeichnet das Ausnutzen einer Sicherheitslücke in Zusammenhang mit SQL Datenbanken, die durch mangelnde Maskierung oder Überprüfung von Metazeichen in Benutzereingaben entsteht. Der Angreifer versucht dabei, über die… …   Deutsch Wikipedia

  • SQL-Injektion — SQL Injection (dt. SQL Einschleusung) bezeichnet das Ausnutzen einer Sicherheitslücke in Zusammenhang mit SQL Datenbanken, die durch mangelnde Maskierung oder Überprüfung von Metazeichen in Benutzereingaben entsteht. Der Angreifer versucht dabei …   Deutsch Wikipedia

  • SQL Injection — (dt. SQL Einschleusung) bezeichnet das Ausnutzen einer Sicherheitslücke in Zusammenhang mit SQL Datenbanken, die durch mangelnde Maskierung oder Überprüfung von Metazeichen in Benutzereingaben entsteht. Der Angreifer versucht dabei, über die… …   Deutsch Wikipedia

Share the article and excerpts

Direct link
Do a right-click on the link above
and select “Copy Link”