Creates rows in a table.
Syntax
INSERT INTO table[@dblink ] [ ( column [, ...] ) ]
{ VALUES ( { expression | DEFAULT } [, ...] )
[ RETURNING return_expression [, ...]
{ INTO { record | variable [, ...] }
| BULK COLLECT INTO collection [, ...] } ]
| query }
Description
You can run the INSERT
command to insert new rows into a table. You can insert one or multiple rows as a
result of a query.
You can list the columns in the order that you desire. Each column that is not in the target list will be inserted with a default value, either its declared default value or null.
If the expression for a column does not use the correct data type, automatic type conversion is attempted.
If the INSERT
command is used within an SPL program and the VALUES
clause is specified, you can specify RETURNINGINTO { record | variable [, ...] }
clause.
If using INSERT
command within an SPL program, you can specify the RETURNING BULK COLLECT INTO collection [, ...]
clause. If you specify multiple collection
as the target of the BULK COLLECT INTO
clause, each collection
must consist of a single scalar field. collection
cannot be a record. For each inserted row, the evaluated value return_expression
is an element in collection
that starts from the first element. Existing rows in collection
are deleted. If the result set is empty, collection
is also empty.
You must have the INSERT
privilege on a table so that you can insert into it. If you use the query
clause to insert rows from a query, you must also have the SELECT
privilege on the table that is used in the query.
Parameters
Parameter | Description |
---|---|
table | The name of an existing table. The name can be schema-qualified. |
dblink | The name of the database link that is used to identify a remote database. For more
information about database links, see the CREATE DATABASE LINK command.
|
column | The name of a column in table .
|
expression | An expression or value to assign to column .
|
DEFAULT | The default value of the column. |
query | A query (the SELECT statement) that provides the rows to be inserted. For more information, see the SELECT command.
|
return_expression | An expression that can include one or more columns from table . If a column name from table is specified in return_expression , the value substituted for the column when return_expression is evaluated is determined as follows:
|
record | A record to whose field you want to assign the evaluation result of return_expression . For example, the first return_expression is assigned to the first field in record , and the second return_expression is assigned to the second field in record . The number of fields in record must match the number of expressions, and the fields
must be type-compatible with corresponding expressions.
|
variable | A variable to which you want to assign the evaluation result of return_expression . If you specify multiple return_expression and variable , the first return_expression is assigned to the first variable , and the second return_expression is assigned to the second variable . The number of the specified variables that follow the INTO keyword must match the number of expressions that follow the RETURNING keyword, and the variables must be type-compatible with corresponding expressions.
|
collection | A collection in which an element is created from the evaluated return_expression . You can specify a collection of a single field or a collection of a record type.
You can also specify multiple collections where each collection consists of a single
field. The number of return expressions must match in number and order of fields in
all specified collections. Each return_expression must be type-compatible with the corresponding collection field.
|
Examples
Insert a single row into the emp
table:
INSERT INTO emp VALUES (8021,'JOHN','SALESMAN',7698,'22-FEB-07',1250,500,30);
In this second example, the column named comm
is omitted. Therefore, it has the default value of null:
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, deptno)
VALUES (8022,'PETERS','CLERK',7698,'03-DEC-06',950,30);
The third example uses the DEFAULT
clause for the hiredate
and comm
columns rather than specifying a value:
INSERT INTO emp VALUES (8023,'FORD','ANALYST',7566,NULL,3000,NULL,20);
This example creates a table for the department names, and then inserts into the table.
The department names are obtained from the dname
column of the dept
table:
CREATE TABLE deptnames (
deptname VARCHAR2(14)
);
INSERT INTO deptnames SELECT dname FROM dept;