Oracle7 Server SQL Reference

INSERT (Embedded SQL)

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

identifies the database on which the INSERT statement is executed. The database can be identified by either:

db_name is a database identifier declared in a previous DECLARE DATABASE statement.

:host_variable is a host variable whose value is a previously declared db_name

If you omit this clause, the INSERT statement is executed on your default database.

FOR :host_integer

limits the number of times the statement is executed if the VALUES clause contains array host variables. If you omit this clause, Oracle7 executes the statement once for each component in the smallest array.

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 in Remote Databases," . 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 query.

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 . Note that the expressions can be host variables with optional indicator variables. You must specify an expression 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

Any host variables that appear in the WHERE clause must be either all scalars or all arrays. If they are scalars, Oracle7 executes the INSERT statement once. If they are arrays, Oracle7 executes the INSERT statement once for each set of array components, inserting one row each time.

Array host variables in the WHERE clause can have different sizes. In this case, the number of times Oracle7 executes the statement is determined by the smaller of the following values:

size of the smallest array

the value of the :host_integer in the optional FOR clause.

For more information on this command, see Programmer's Guide to the Oracle Precompilers.

Example I

This example illustrates the use of the embedded SQL INSERT command:

EXEC SQL INSERT INTO emp (ename, empno, sal)

        VALUES (:ename, :empno, :sal);

Example II

This example shows an embedded SQL INSERT command with a subquery:

EXEC SQL INSERT INTO new_emp (ename, empno, sal)

			SELECT ename, empno, sal FROM emp