Adds one or more rows to a table.
Syntax
INSERT INTO table[subquery][@dblink] [ ( column [, ...] ) ]
{ VALUES ( { expression | DEFAULT } [, ...] )
[ RETURNING return_expression [, ...]
{ INTO { record | variable [, ...] }
| BULK COLLECT INTO collection [, ...] } ]
| query }Prerequisites
To insert rows into a table, you must have the INSERT privilege on that table. If you use a query to provide the rows, you must also have the SELECT privilege on the table referenced in the query.
Description
Use the INSERT statement to add a single row or multiple rows to a table. To insert multiple rows at once, supply them through a subquery.
List target columns in any order. Columns not listed receive their declared default value, or null if no default is defined. If an expression does not match the column's data type, implicit type conversion is attempted.
RETURNING clause
The RETURNING clause returns computed values based on the rows just inserted. Use it to retrieve auto-generated values such as sequence numbers or default timestamps without a separate query.
`RETURNING INTO { record | variable [, ...] }` — valid only inside an SPL program when using the
VALUESclause.`RETURNING BULK COLLECT INTO collection [, ...]` — valid only inside an SPL program.
For BULK COLLECT INTO with multiple collections: each collection must consist of a single scalar field, not a record type. For each inserted row, the evaluated return_expression populates an element in the collection starting from the first element. Existing elements in the collection are deleted before population. If the result set is empty, the collection is also empty.
Parameters
Basic insert parameters
| Parameter | Description |
|---|---|
table | The name of an existing table. The name can be schema-qualified. |
subquery | A subquery used as the insert target. |
dblink | The database link name identifying a remote database. See the CREATE DATABASE LINK statement for details. |
column | The name of a column in table. |
expression | A value or expression to assign to column. |
DEFAULT | Inserts the column's declared default value. |
query | A SELECT statement that provides the rows to insert. See the SELECT statement for details. |
RETURNING clause parameters
| Parameter | Description |
|---|---|
return_expression | An expression that can reference one or more columns in table. The substituted value depends on whether the column was assigned in the INSERT statement: if assigned, that value is used; if not assigned and no column default exists, null is used; if not assigned and a column default exists, the default value is used. |
record | A record whose fields receive the evaluated return_expression values. The first return_expression maps to the first field, the second to the second, and so on. The number of fields and their types must match the expressions. |
variable | A variable that receives the evaluated return_expression value. When multiple variables follow INTO, they map positionally to the expressions following RETURNING. The count and types must match. |
collection | A collection populated with the evaluated return_expression values. Accepts a single-field collection, a record-type collection, or multiple single-field collections. The number of return expressions must match the total number of fields across all specified collections, and each expression must be type-compatible with its corresponding collection field. |
Examples
Insert a single row
Specify values for all columns in the order they appear in the table:
INSERT INTO emp VALUES (8021,'JOHN','SALESMAN',7698,'22-FEB-07',1250,500,30);Omit a column to use its default value
List only the columns you want to set. Unlisted columns receive their default value or null. Here, comm is omitted and defaults to null:
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, deptno)
VALUES (8022,'PETERS','CLERK',7698,'03-DEC-06',950,30);Use null for specific columns
Pass NULL directly for columns where you want null rather than a default:
INSERT INTO emp VALUES (8023,'FORD','ANALYST',7566,NULL,3000,NULL,20);Insert rows from a query
Create a table and populate it from a SELECT result. The following example copies all department names from the dept table:
CREATE TABLE deptnames (
deptname VARCHAR2(14)
);
INSERT INTO deptnames SELECT dname FROM dept;Use a subquery as the insert target
Specify a subquery in place of a table name to insert rows into the result set of that query:
INSERT INTO (SELECT * FROM table1) VALUES (1, '1', 1);