INSERT v15

Suggest edits

Name

INSERT — Create rows in a table.

Synopsis

INSERT INTO <table>[@<dblink> | [ AS ] <alias> ] [ ( <column> [, ...] ) ]
  { VALUES ( { <expression> | DEFAULT } [, ...] )
    [ RETURNING <return_expression> [, ...]
        { INTO { <record> | <variable> [, ...] }
        | BULK COLLECT INTO <collection> [, ...] } ]
  | <query> }

Description

INSERT allows you to insert new rows into a table. You can insert a single row or several rows as a result of a query.

You can list the columns in the target list in any order. Each column not present in the target list is inserted using a default value, either its declared default value or null.

If the expression for each column doesn't have the correct data type, type conversion is attempted.

You can specify the RETURNING INTO { record | variable [, ...] } clause only when the INSERT command is used in an SPL program and only when the VALUES clause is used.

You can specify the RETURNING BULK COLLECT INTO collection [, ...] clause only if the INSERT command is used in an SPL program. If you specify more than one collection as the target of the BULK COLLECT INTO clause, then each collection must consist of a single, scalar field. That is, collection can't be a record. The return_expression evaluated for each inserted row becomes an element in collection, starting with the first element. Any existing rows in collection are deleted. If the result set is empty, then collection is empty.

You need INSERT privilege to a table to insert into it. If you use the query clause to insert rows from a query, you also need SELECT privilege on any table used in the query.

Parameters

table

The name (optionally schema-qualified) of an existing table.

alias

A substitute name, which is an alias for the table, view, materialized view, or a subquery to reference in a statement during query execution.

dblink

Database link name identifying a remote database. See CREATE DATABASE LINK for information on database links.

column

The name of a column in table.

expression

An expression or value to assign to column.

DEFAULT

This column is filled with its default value.

query

A query (SELECT statement) that supplies the rows to insert. See SELECT for a description of the syntax.

return_expression

An expression that can include one or more columns from table. If you specify a column name from table in return_expression, the value substituted for the column when return_expression is evaluated is determined as follows:

  • If the column specified in return_expression is assigned a value in the INSERT command, then the assigned value is used in the evaluation of return_expression.

  • If the column specified in return_expression isn't assigned a value in the INSERT command, and there's no default value for the column in the table’s column definition, then null is used in the evaluation of return_expression.

  • If the column specified in return_expression isn't assigned a value in the INSERT command, and there's a default value for the column in the table’s column definition, then the default value is used in the evaluation of return_expression.

record

A record whose field to assign the evaluated return_expression. The first return_expression is assigned to the first field in record, the second return_expression is assigned to the second field in record, and so on. The number of fields in record must exactly match the number of expressions, and the fields must be type-compatible with their assigned expressions.

variable

A variable to which to assign the evaluated return_expression. If you specify more than one return_expression and variable, the first return_expression is assigned to the first variable, the second return_expression is assigned to the second variable, and so on. The number of variables specified following the INTO keyword must exactly match the number of expressions following the RETURNING keyword. The variables must be type-compatible with their assigned expressions.

collection

A collection in which an element is created from the evaluated return_expression. There can be either:

  • A single collection, which can be a collection of a single field or a collection of a record type.

  • More than one collection, in which case each collection must consist of a single field.

    The number of return expressions must match in number and order the number of fields in all specified collections. Each corresponding return_expression and collection field must be type-compatible.

Examples

Insert a single row into table emp:

INSERT INTO emp VALUES (8021,'JOHN','SALESMAN',7698,'22-FEB-07',1250,500,30);

This example omits the column comm, which means it uses 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);

This 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 by selecting from the dname column of the dept table:

CREATE TABLE deptnames (
    deptname        VARCHAR2(14)
);
INSERT INTO deptnames SELECT dname FROM dept;

This example creates an alias enm for the table emp and inserts rows into a table:

INSERT INTO emp AS enm (enm.empno, enm.ename, enm.job, enm.mgr, enm.hiredate, enm.sal, enm.deptno)
  VALUES (7499, 'SMITH', 'ANALYST', 7902, '03-DEC-06', 2500, 20);
← Prev

GRANT

Next →

MERGE