INSERT

The INSERT statement adds rows to a table .

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

TO_CHAR
TO_DATE
Sequence NEXTVAL and Sequence CURRVAL
CAST
DEFAULT
SYSDATE and GETDATE
USER functions
Expressions
SYSTEM_USER

Required privilege

No privilege is required for the table owner.

INSERT for another user's table.

SQL syntax

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

The SingleRowValues parameter has the syntax:

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

Parameters

Parameter	Description
`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`	Place holder 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".
`DEFAULT`	Specifies that the column should be updated with the default value.
`Expression`	Valid expression syntax. See Chapter 3, "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');

:pno and :pname are dynamic parameters whose 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