SQL is a programming language designed for managing data in relational database management systems ....
INSERT statement adds one or more records to any single table
Table (database)
In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
A relational database is a database that conforms to relational model theory. The software used in a relational database is called a relational database management system . Colloquial use of the term "relational database" may refer to the RDBMS software, or the relational database itself...
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
Check Constraint
A check constraint is a condition that defines valid data when adding or updating an entry in a table of a relational database. A check constraint is applied to each row in the table. The constraint must be a predicate. It can refer to a single or multiple columns of the table...
Null is a special marker used in Structured Query Language to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL Null serves to fulfill the requirement that all true relational database management systems support...
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');
SQL-92 was the third revision of the SQL database query language. Unlike SQL-89, it was a major revision of the standard. For all but a few minor incompatibilities, the SQL-89 standard is forwards-compatible with SQL-92....
) is the use of row value constructors to insert multiple rows at a time in a single SQL statement:
The IBM DB2 Enterprise Server Edition is a relational model database server developed by IBM. It primarily runs on Unix , Linux, IBM i , z/OS and Windows servers. DB2 also powers the different IBM InfoSphere Warehouse editions...
Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
PostgreSQL, often simply Postgres, is an object-relational database management system available for many platforms including Linux, FreeBSD, Solaris, MS Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software...
MySQL officially, but also commonly "My Sequel") is a relational database management system that runs as a server providing multi-user access to a number of databases. It is named after developer Michael Widenius' daughter, My...
H2 is a relational database management system written in Java. It can be embedded in Java applications or run in the client-server mode. The disk footprint is about 1 MB....
.
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
Database trigger
A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for keeping the integrity of the information on the database...
) 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
SQL:2003
SQL:2003 is the fifth revision of the SQL database query language. The latest revision of the standard is SQL:2008.-Summary:The SQL:2003 standard makes minor modifications to all parts of SQL:1999 , and officially introduces a few new features such as:* XML-related features * Window functions* the...
The DUAL table is a special one-row table present by default in all Oracle database installations. It is suitable for use in selecting a pseudocolumn such as SYSDATE or USER.The table has a single VARCHAR2 column called DUMMY that has a value of 'X'....
, 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 "INSERT ALL" statement, where multiple insert statements are terminated by a SELECT:
INSERT ALL
INTO phone_book VALUES ('John Doe', '555-1212')
INTO phone_book VALUES ('Peter Doe', '555-2323')
SELECT * FROM DUAL;
Firebird is an open source SQL relational database management system that runs on Linux, Windows, and a variety of Unix. The database forked from Borland's open source edition of InterBase in 2000, but since Firebird 1.5 the code has been largely rewritten ....
inserting multiple rows can be achieved like this:
INSERT INTO phone_book ("name", "number")
SELECT 'John Doe', '555-1212' FROM RDB$DATABASE UNION ALL
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 contexts that can be used in a single query.
Copying rows from other tables
An INSERT 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 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.
A surrogate 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 derived from application data.- Definition :There are at least two definitions of a surrogate:...
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:
A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure is actually stored in the database data dictionary.Typical uses for stored procedures include data validation or access control mechanisms...
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.
The SQL SELECT statement returns a result set of records from one or more tables.A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used Data Manipulation Language command...
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’));
The SQL SELECT statement returns a result set of records from one or more tables.A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used Data Manipulation Language command...
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
MySQL
MySQL officially, but also commonly "My Sequel") is a relational database management system that runs as a server providing multi-user access to a number of databases. It is named after developer Michael Widenius' daughter, My...
.
Using a unique combination of elements from the original SQL INSERT in a subsequent SELECT
Select (SQL)
The SQL SELECT statement returns a result set of records from one or more tables.A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used Data Manipulation Language command...
statement.
Using a GUID in the SQL INSERT statement and retrieving it in a SELECT
Select (SQL)
The SQL SELECT statement returns a result set of records from one or more tables.A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used Data Manipulation Language command...
statement.
Using the OUTPUT clause in the SQL INSERT statement for SQL Server 2005 and SQL SERVER 2008.
Using an INSERT statement with RETURNING clause for Oracle
Oracle database
The Oracle Database is an object-relational database management system produced and marketed by Oracle Corporation....
.
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
PostgreSQL
PostgreSQL, often simply Postgres, is an object-relational database management system available for many platforms including Linux, FreeBSD, Solaris, MS Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software...
(since 8.2). The returned list is identical to the result of a SELECT.
Firebird is an open source SQL relational database management system that runs on Linux, Windows, and a variety of Unix. The database forked from Borland's open source edition of InterBase in 2000, but since Firebird 1.5 the code has been largely rewritten ....
has the same syntax; the statement may add at most one row.
INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' )
RETURNING phone_book_id
H2 is a relational database management system written in Java. It can be embedded in Java applications or run in the client-server mode. The disk footprint is about 1 MB....
A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for keeping the integrity of the information on the database...
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.
