Insert (SQL)


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

Basic form

Insert statements have the following form:
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 by the INSERT statement must satisfy all the applicable 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 VALUES ;

Shorthand may also be used, taking advantage of the order of the columns when the table was created. It is not required to specify all columns in the table since any other columns will take their default value or remain null:
Example for inserting data into 2 columns in the phone_book table and ignoring any other columns which may be after the first 2 in the table.

INSERT INTO phone_book VALUES ;

Advanced forms

Multirow inserts

A SQL feature is the use of row value constructors to insert multiple rows at a time in a single SQL statement:

INSERT INTO tablename
VALUES,
,
...

This feature is supported by DB2, SQL Server, PostgreSQL, MySQL, sqlite and H2.
Example :

INSERT INTO phone_book VALUES, ;

which may be seen as a shorthand for the two statements

INSERT INTO phone_book VALUES ;
INSERT INTO phone_book VALUES ;

Note that the two separate statements may have different semantics 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 DUAL

A 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 AS t
UNION ALL
SELECT 'Peter Doe','555-2323' FROM LATERAL AS t

Oracle PL/SQL supports the "INSERT ALL" statement, where multiple insert statements are terminated by a SELECT:

INSERT ALL
INTO phone_book VALUES
INTO phone_book VALUES
SELECT * FROM DUAL;

In Firebird inserting multiple rows can be achieved like this:

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

Firebird however restricts the number of rows than can be inserted in this way, since there is a limit to the number of contexts that can be used in a single query.

Copying rows from other tables

An statement can also be used to retrieve data from other tables, 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 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

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.

INSERT INTO phone_book2
SELECT ,
FROM phone_book
WHERE name IN

The SELECT statement produces a table, and the schema of that temporary table must match with the schema of the table where the data is inserted into.

Default Values

It is possible to insert a new row without specifying any data, using default values for all columns.
However some databases would reject the statement if no data is given, such as Microsoft SQL Server, and in this case the keyword can be used.

INSERT INTO phone_book
VALUES

Sometimes a databases also support alternative syntax for this, for example MySQL allows obmitting the DEFAULT keyword, and T-SQL can use insteads of.
The keyword can also be used in normal insertion to explicitly fill a column using that column's default value:

INSERT INTO phone_book VALUES

What happens when a column does not specify a default value is database dependent.
For example, MySQL and SQLite will fills in with blank value, while many other databases will reject the statement.

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 an 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:
SELECT *
FROM NEW TABLE AS t

SELECT EMPNO, HIRETYPE, HIREDATE
FROM FINAL TABLE
VALUES
);

INSERT INTO phone_book VALUES
RETURNING phone_book_id INTO v_pb_id

INSERT INTO phone_book VALUES
RETURNING phone_book_id

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 mechanism.