INSERT

The INSERT statement adds rows to a table.

The following expressions can be used in the VALUES clause of an INSERT statement:

Required privilege

No privilege is required for the table owner.

INSERT for another user's table.

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL syntax

INSERT [hint] INTO [Owner.]TableName [(Column [,...])]
VALUES (SingleRowValues)
[RETURNING|RETURN Expression[,...] INTO DataItem[,...]]

The SingleRowValues parameter has the syntax: 

{NULL|{?|:DynamicParameter}|{Constant}| DEFAULT}[,...]

Parameters

Parameter Description

hint

Specifies a statement level optimizer hint for the INSERT statement. See "Statement Level Optimizer Hints" for information on statement level optimizer hints.

Owner

The owner of the table into which data is inserted.

TableName

Name of the table into which data is inserted.

Column

Each column in this list is assigned a value from SingleRowValues.

If you omit one or more of the table's columns from this list, then the value of the omitted column in the inserted row is the column default value as specified when the table was created or last altered. If any omitted column has a NOT NULL constraint and has no default value, then the database returns an error.

If you omit a list of columns completely, then you must specify values for all columns in the table.

?

:DynamicParameter

Placeholder for a dynamic parameter in a prepared SQL statement. The value of the dynamic parameter is supplied when the statement is executed.

Constant

A specific value. See "Constants" for information on constants.

DEFAULT

Specifies that the column should be updated with the default value.

Expression

Valid expression syntax. See Expressions for information on expressions.

DataItem

Host variable or PL/SQL variable that stores the retrieved Expression value.

Description

  • If you omit any of the table's columns from the column name list, the INSERT statement places the default value in the omitted columns. If the table definition specifies NOT NULL for any of the omitted columns and there is no default value, the INSERT statement fails.

  • BINARY and VARBINARY data can be inserted in character or hexadecimal format:

    • Character format requires single quotes.

    • Hexadecimal format requires the prefix 0x before the value.

  • The INSERT operation fails if it violates a foreign key constraint. See "CREATE TABLE" for a description of the foreign key constraint.

  • Restrictions on the RETURNING clause:

    • Each Expression must be a simple expression. Aggregate functions are not supported.

    • You cannot return a sequence number into an OUT parameter.

    • ROWNUM and subqueries cannot be used in the RETURNING clause.

    • Parameters in the RETURNING clause cannot be duplicated anywhere in the INSERT statement.

    • In PL/SQL, you cannot use a RETURNING clause with a WHERE CURRENT operation.

Examples

A new single row is added to the purchasing.vendors table. 

INSERT INTO purchasing.vendors
VALUES (9016,
       'Secure Systems, Inc.',
       'Jane Secret',
       '454-255-2087',
       '1111 Encryption Way',
       'Hush',
       'MD',
       '00007',
       'discount rates are secret');

For dynamic parameters :pno and :pname, values are supplied at runtime. 

INSERT INTO purchasing.parts (partnumber, partname)
  VALUES (:pno, :pname);

Return the annual salary and job_id of a new employee. Declare the variables sal and jobid with the same data types as salary and job_id. Insert the row into employees. Print the variables for verification. 

Command> VARIABLE sal12 NUMBER(8,2);
Command> VARIABLE jobid VARCHAR2(10) INLINE NOT NULL;

Command> INSERT INTO employees(employee_id, last_name, email, hire_date, 
           job_id, salary)
         VALUES (211,'Doe','JDOE',sysdate,'ST_CLERK',2400)
         RETURNING salary*12, job_id INTO :sal12,:jobid;
1 row inserted.

PRINT sal12 jobid;
SAL12                 : 28800
JOBID                 : ST_CLERK

See also