Insert (SQL)

Encyclopedia : I : IN : INS : Insert (SQL)

An SQL INSERT statement adds a record to a table in a relational database.

Contents

1 Basic form
2 Optional forms
3 Retrieving the key
4 See also

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.

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

Optional forms

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

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

This optional feature is supported by DB2 and MySQL.

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

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

Example (creating records in one table from another table / copying records using insert):

INSERT INTO phone_book2 SELECT * FROM phone_book
WHERE NAME IN ('John Doe', 'Peter Doe')

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 query for use in another SQL INSERT query. Since SQL INSERT does not return row data, it becomes necessary to implement a workaround to such scenarios. Common implementations include:

Using a database-specific stored procedure.
Using a database-specific SELECT query on a temporary table containing last inserted row(s).
Using a unique combination of elements from the original SQL INSERT in a SELECT statement.
Using a GUID in the SQL INSERT query and retrieving it in a SELECT statement.

Insert (SQL)

Basic form

Optional forms

Retrieving the key

See also