Adds 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 }

Description

You can execute the INSERT statement to insert new rows into a table. You can insert a single row at a time or multiple rows as a result of a query.

You can list the columns in the list in the order that you desire. Each column that is not in the 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.

Only when the INSERT statement is executed within an SPL program and the VALUES clause is used, you can specify the RETURNINGINTO { record | variable [, ...] } clause.

Only when the INSERT statement is executed within an SPL program, you can specify the RETURNING BULK COLLECT INTO collection [, ...] clause. If you specify multiple collection as the object of the BULK COLLECT INTO clause, each collection must consist of a single scalar field. Each 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 database link name, which identifies a remote database. For more information about database links, see the CREATE DATABASE LINK statement.
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 statement.
return_expression An expression that can include one or more columns in 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:
  • If you assign a value in the INSERT statement to the specified column in return_expression, the assigned value is used to evaluate return_expression.
  • If you do not assign a value in the INSERT statement to the specified column in return_expression and no default value is provided for the column definition, null is used to evaluate return_expression.
  • If you do not assign a value in the INSERT statement to the specified column in return_expression and a default value is provided for the column definition, the default value is used to evaluate return_expression.
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 the 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 the number of fields in all specified collections. Each return_expression must be type-compatible with the corresponding collection field.
subquery This parameter specifies a subquery clause.

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;

Use the result of the select * from table1 as an object and insert a row of (1,1,1) into the object: 

INSERT INTO (SELECT * FROM table1) VALUES (1, '1', 1) ;