An
SQL INSERT statement adds one or more records to any single
table
Table may refer to:
* Table (furniture), a piece of furniture with a flat surface and one or more legs
* Table (landform), a flat area of land
* Table (information), a data arrangement with rows and columns
* Table (database), how the table data ...
in a
relational database
A relational database is a (most commonly digital) database based on the relational model of data, as proposed by E. F. Codd in 1970. A system used to maintain relational databases is a relational database management system (RDBMS). Many relatio ...
.
Basic form
Insert statements have the following form:
The number of
columns
A column or pillar in architecture and structural engineering is a structural element that transmits, through compression, the weight of the structure above to other structural elements below. In other words, a column is a compression member. ...
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 statement must satisfy all the applicable constraints (such as
primary key
In the relational model of databases, a primary key is a ''specific choice'' of a ''minimal'' set of attributes (Column (database), columns) that uniquely specify a tuple (Row (database), row) in a Relation (database), relation (Table (database), t ...
s,
constraints, and 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
Null may refer to:
Science, technology, and mathematics Computing
* Null (SQL) (or NULL), a special marker and keyword in SQL indicating that something has no value
* Null character, the zero-valued ASCII character, also designated by , often use ...
:
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
Multirow inserts
A SQL feature (since
SQL-92
SQL-92 was the third revision of the SQL database query language. Unlike SQL-89, it was a major revision of the standard. Aside from a few minor incompatibilities, the SQL-89 standard is forward-compatible with SQL-92.
The standard specificatio ...
) is the use of ''row value constructors'' to insert multiple rows at a time in a single SQL statement:
INSERT INTO tablename (column-a, olumn-b, ...
VALUES ('value-1a', value-1b', ...,
('value-2a', value-2b', ...,
...
This feature is supported by
IBM Db2
Db2 is a family of data management products, including database servers, developed by IBM. It initially supported the relational model, but was extended to support object–relational features and non-relational structures like JSON a ...
,
SQL Server (since version 10.0 - i.e. 2008),
PostgreSQL
PostgreSQL (, ), also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the In ...
(since version 8.2),
MySQL
MySQL () is an open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter My, and "SQL", the acronym for Structured Query Language. A relational database o ...
,
SQLite
SQLite (, ) is a database engine written in the C programming language. It is not a standalone app; rather, it is a library that software developers embed in their apps. As such, it belongs to the family of embedded databases. It is the most ...
(since version 3.7.11) 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 (
SQL:2003) due to the incomplete subselect clause.
To do the same in Oracle use the
DUAL table The DUAL table is a special one-row, one-column table present by default in Oracle and other database installations. In Oracle, the table has a single VARCHAR2(1) column called DUMMY that has a value of 'X'. It is suitable for use in selecting a pse ...
, 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 ( VALUES (1) ) AS t(c)
UNION ALL
SELECT 'Peter Doe','555-2323' FROM LATERAL ( VALUES (1) ) AS t(c)
Oracle PL/SQL supports the statement, where multiple insert statements are terminated by a :
INSERT ALL
INTO phone_book VALUES ('John Doe', '555-1212')
INTO phone_book VALUES ('Peter Doe', '555-2323')
SELECT * FROM DUAL;
In
Firebird
Firebird and fire bird may refer to:
Mythical birds
* Phoenix (mythology), sacred firebird found in the mythologies of many cultures
* Bennu, Egyptian firebird
* Huma bird, Persian firebird
* Firebird (Slavic folklore)
Bird species
''Various sp ...
inserting multiple rows can be achieved like this:
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, 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 ('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'
schema
The word schema comes from the Greek word ('), which means ''shape'', or more generally, ''plan''. The plural is ('). In English, both ''schemas'' and ''schemata'' are used as plural forms.
Schema may refer to:
Science and technology
* SCHEMA ...
s are not the same.)
INSERT INTO phone_book2 (name, number)
SELECT name, number
FROM phone_book
WHERE name IN ('John Doe', 'Peter Doe')
The 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.
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 keyword can be used.
INSERT INTO phone_book
VALUES ( DEFAULT )
Sometimes databases also support alternative syntax for this; for example, MySQL allows omitting the keyword, and T-SQL can use instead 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 ( 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
A surrogate key (or synthetic key, pseudokey, entity identifier, factless key, or technical key) in a database is a unique identifier for either an ''entity'' in the modeled world or an ''object'' in the database. The surrogate key is ''not'' deri ...
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 statement for use in other SQL statements. Most systems do not allow SQL 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
A stored procedure (also termed proc, storp, sproc, StoPro, StoredProc, StoreProc, sp, or SP) is a subroutine available to applications that access a relational database management system (RDBMS). Such procedures are stored in the database data dic ...
that generates the surrogate key, performs the operation, and finally returns the generated key. For example, in Microsoft SQL Server, the key is retrieved via the special function, while in SQLite the function is named .
* Using a database-specific 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
z/OS is a 64-bit operating system for IBM z/Architecture mainframes, introduced by IBM in October 2000. It derives from and is the successor to OS/390, which in turn was preceded by a string of MVS versions.Starting with the earliest:
* O ...
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 statement after the statement with a database-specific function that returns the generated primary key for the most recently inserted row. For example, for
MySQL
MySQL () is an open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter My, and "SQL", the acronym for Structured Query Language. A relational database o ...
.
* Using a unique combination of elements from the original SQL in a subsequent statement.
* Using a
GUID
A universally unique identifier (UUID) is a 128-bit label used for information in computer systems. The term globally unique identifier (GUID) is also used.
When generated according to the standard methods, UUIDs are, for practical purposes, uni ...
in the SQL statement and retrieving it in a statement.
* Using the clause in the SQL statement for MS-SQL Server 2005 and MS-SQL Server 2008.
* Using an statement with clause for
Oracle
An oracle is a person or agency considered to provide wise and insightful counsel or prophetic predictions, most notably including precognition of the future, inspired by deities. As such, it is a form of divination.
Description
The word '' ...
.
*:
INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' )
RETURNING phone_book_id INTO v_pb_id
* Using an statement with clause for
PostgreSQL
PostgreSQL (, ), also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the In ...
(since 8.2). The returned list is identical to the result of a .
**
Firebird
Firebird and fire bird may refer to:
Mythical birds
* Phoenix (mythology), sacred firebird found in the mythologies of many cultures
* Bennu, Egyptian firebird
* Huma bird, Persian firebird
* Firebird (Slavic folklore)
Bird species
''Various sp ...
has the same syntax in Data Modification Language statements (DSQL); the statement may add at most one row. In stored procedures, triggers and execution blocks (PSQL) the aforementioned Oracle syntax is used.
**:
INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' )
RETURNING phone_book_id
* Using the function in
H2 returns the last identity inserted.
*:
SELECT IDENTITY();
Triggers
If
triggers are defined on the table on which the statement operates, those triggers are evaluated in the context of the operation. triggers allow the modification of the values that shall be inserted into the table. 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)*
ttps://dev.mysql.com/doc/refman/5.5/en/insert.html MySQL statement (MySQL 5.5 Reference Manual)
{{DEFAULTSORT:Insert (Sql)
SQL keywords
Articles with example SQL code