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'', ''column3'' ... ]) VALUES (''value1'' [, ''value2'', ''value3'' ... ])

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');

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:

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

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 ('John Doe', '555-1212');

Advanced forms

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

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');

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

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)

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

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

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')

Default Values

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

INSERT INTO phone_book
VALUES ( DEFAULT )

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

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

What happens when a column does not specify a default value is database dependent. For example, MySQL and SQLite will fill in with a blank value (except when in strict mode), 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 other 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, while in SQLite the function is named last_insert_rowid().
• 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 t
  

  • Db2 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. For example, LAST_INSERT_ID() for MySQL.
• 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 OUTPUT clause in the SQL INSERT statement for MS-SQL Server 2005 and MS-SQL Server 2008.
• Using an INSERT statement with RETURNING clause for Oracle.

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

• Using an INSERT statement with RETURNING clause for PostgreSQL (since 8.2). The returned list is identical to the result of a INSERT.
  • Firebird has the same syntax in Data Modification Language statements (DSQL); the statement may add at most one row.^[2] In stored procedures, triggers and execution blocks (PSQL) the aforementioned Oracle syntax is used.^[3]

    INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' )
    RETURNING phone_book_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 mechanism.

References

External links

• Oracle SQL INSERT statement (Oracle Database SQL Language Reference, 11g Release 2 (11.2) on oracle.com)
• Microsoft Access Append Query Examples and SQL INSERT Query Syntax
• MySQL INSERT statement (MySQL 5.5 Reference Manual)