Oracle7 Server SQL Reference

INSERT

Purpose

To add rows to a table or to a view's base table.

Prerequisites

For you to insert rows into a table, the table must be in your own schema or you must have INSERT privilege on the table.

For you to insert rows into the base table of a view, the owner of the schema containing the view must have INSERT privilege on the base table. Also, if the view is in a schema other than your own, you must have INSERT privilege on the view.

The INSERT ANY TABLE system privilege also allows you to insert rows into any table or any view's base table.

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the creation label of the table or view:

If the creation label of the table or view is higher than your DBMS label, you must have WRITEUP system privileges.

If the creation label of the table or view is lower than your DBMS label, you must have WRITEDOWN system privilege.

If the creation label of your table or view is noncomparable to your DBMS label, you must have WRITEUP and WRITEDOWN system privileges.

Syntax

Keywords and Parameters

schema

is the schema containing the table or view. If you omit schema, Oracle7 assumes the table or view is in your own schema.

table view

is the name of the table into which rows are to be inserted. If you specify view, Oracle7 inserts rows into the view's base table.

dblink

is a complete or partial name of a database link to a remote database where the table or view is located. For information on referring to database links, see the section "Referring to Objects" . You can only insert rows into a remote table or view if you are using Oracle7 with the distributed option.

If you omit dblink, Oracle7 assumes that the table or view is on the local database.

subquery_1

is a subquery that Oracle treats in the same manner as a view. For the syntax of subquery, see .

column

is a column of the table or view. In the inserted row, each column in this list is assigned a value from the VALUES clause or the subquery.

If you omit one of the table's columns from this list, the column's value for the inserted row is the column's default value as specified when the table was created. If you omit the column list altogether, the VALUES clause or query must specify values for all columns in the table.

VALUES

specifies a row of values to be inserted into the table or view. See the syntax description of expr . You must specify a value in the VALUES clause for each column in the column list.

subquery_2

is a subquery that returns rows that are inserted into the table. The select list of this subquery must have the same number of columns as the column list of the INSERT statement. For the syntax description of subquery, see .

Usage Notes

An INSERT statement with a VALUES clause adds a single row to the table. This row contains the values specified in the VALUES clause.

An INSERT statement with a subquery instead of a VALUES clause adds to the table all rows returned by the subquery. Oracle7 processes the subquery and inserts each returned row into the table. If the subquery selects no rows, Oracle7 inserts no rows into the table. The subquery can refer to any table, view, or snapshot, including the target table of the INSERT statement.

The number of columns in the column list of the INSERT statement must be the same as the number of values in the VALUES clause or the number of columns selected by the subquery. If you omit the column list, then the VALUES clause or the subquery must provide values for every column in the table. If you are using Trusted Oracle7 in DBMS MAC mode and you omit a value for the ROWLABEL column, the new row is automatically labeled with your DBMS label.

Oracle7 assigns values to fields in new rows based on the internal position of the columns in the table and the order of the values in the VALUES clause or in the select list of the query. You can determine the position of each column in the table by examining the data dictionary. See the "Data Dictionary" chapter in Oracle7 Server Reference.

If you omit any columns from the column list, Oracle7 assigns them their default values as specified when the table was created. For more information on the default column values, see the CREATE TABLE command . If any of these columns has a NOT NULL constraint, then Oracle7 returns an error indicating that the constraint has been violated and rolls back the INSERT statement.

Issuing an INSERT statement against a table fires any INSERT triggers defined on the table.

Inserting Into Views

If a view was created using the WITH CHECK OPTION, then you can only insert rows into the view that satisfy the view's defining query.

You cannot insert rows into a view if the view's defining query contains one of the following constructs:

join

set operator

GROUP BY clause

group function

DISTINCT operator

Example I

The following statement inserts a row into the DEPT table:

INSERT INTO dept 	
	VALUES (50, 'PRODUCTION', 'SAN FRANCISCO')

Example II

The following statement inserts a row with six columns into the EMP table. One of these columns is assigned NULL and another is assigned a number in scientific notation:

INSERT INTO emp (empno, ename, job, sal, comm, deptno) 
	VALUES (7890, 'JINKS', 'CLERK', 1.2E3, NULL, 40)

Example III

The following statement has the same effect as Example II:

INSERT INTO (select empno, job, sal, comm, deptno from emp)
	VALUES (7890, 'JINKS', 'CLERK', 1.2E3, NULL, 40)

Example IV

The following statement copies managers and presidents or employees whose commission exceeds 25% of their salary into the BONUS table:

INSERT INTO bonus 
	SELECT ename, job, sal, comm 
		FROM emp 
		WHERE comm > 0.25 * sal 
		   OR job IN ('PRESIDENT', 'MANAGER')

Example V

The following statement inserts a row into the ACCOUNTS table owned by the user SCOTT on the database accessible by the database link SALES:

INSERT INTO scott.accounts@sales (acc_no, acc_name) 
	VALUES (5001, 'BOWER')

Assuming that the ACCOUNTS table has a BALANCE column, the newly inserted row is assigned the default value for this column because this INSERT statement does not specify a BALANCE value.

Example VI

The following statement inserts a new row containing the next value of the employee sequence into the EMP table:

INSERT INTO emp

	VALUES	(empseq.nextval, 'LEWIS', 'CLERK',

			 7902, SYSDATE, 1200, NULL, 20)