1 – column-name
Specifies a list of names of columns in the table or view. You
can list the columns in any order, but the names must correspond
to those of the table or view.
If you do not include all the column names in the list, SQL
assigns a null value to those not specified, unless columns were:
o Defined with a default
o Based on a domain that has a default
o Defined with the NOT NULL clause in the CREATE TABLE statement
You cannot omit from an INSERT statement the names of columns
defined with the NOT NULL clause. If you do, the statement fails.
Omitting the list of column names altogether is the same as
listing all the columns of the table or view in the same order
as they were defined.
You must omit the list of column names when using the INSERT
statement to assign values to the segments in a column of data
type LIST OF BYTE VARYING. Column names are not valid in this
context.
2 – CURSOR cursor name
Keyword required when using cursors. You must use a cursor to
insert values into any row that contains a column of the LIST OF
BYTE VARYING data type.
3 – DEFAULT
Forces the named column to assume the default value defined for
that column (or NULL if none is defined).
If the DEFAULT clause is used in an INSERT statement then one of
the following will be applied:
o If a DEFAULT attribute is present for the column then that
value will be applied during INSERT.
o Else if an AUTOMATIC attribute is present for the column then
that value will be applied during INSERT. This can only happen
if the SET FLAGS 'AUTO_OVERRIDE' is used since during normal
processing these columns are read-only.
o Otherwise a NULL will be applied during INSERT.
4 – DEFAULT_VALUES
Specifies that every column in the table is assigned the default
value (or NULL, if the column has no default value).
5 – INTO parameter
Inserts the value specified to a specified parameter. The INTO
parameter clause is not valid in interactive SQL.
6 – INTO
Syntax options:
INTO table-name | INTO view-name
The name of the target table or view to which you want to add a
row. Inserts the value specified to a specified parameter. The
INTO parameter clause is not valid in interactive SQL.
7 – limit-to-clause
See Select_Expressions in the Oracle Rdb SQL Reference Manual for
a description of the LIMIT TO expression.
8 – OPTIMIZE AS query name
The OPTIMIZE AS clause assigns a name to the query. Use the SET
FLAGS 'STRATEGY' to see this name displayed.
9 – OPTIMIZE_FOR
The OPTIMIZE FOR clause specifies the preferred optimizer
strategy for statements that specify a select expression. The
following options are available:
o FAST FIRST
A query optimized for FAST FIRST returns data to the user as
quickly as possible, even at the expense of total throughput.
If a query can be cancelled prematurely, you should specify
FAST FIRST optimization. A good candidate for FAST FIRST
optimization is an interactive application that displays
groups of records to the user, where the user has the option
of aborting the query after the first few screens. For
example, singleton SELECT statements default to FAST FIRST
optimization.
If optimization strategy is not explicitly set, FAST FIRST is
the default.
o TOTAL TIME
If your application runs in batch, accesses all the records in
the query, and performs updates or writes a report, you should
specify TOTAL TIME optimization. Most queries benefit from
TOTAL TIME optimization.
o SEQUENTIAL ACCESS
Forces the use of sequential access. This is particularly
valuable for tables that use the strict partitioning
functionality.
10 – OPTIMIZE USING outline name
The OPTIMIZE USING clause explicitly names the query outline to
be used with the select expression even if the outline ID for the
select expression and for the outline are different.
See the CREATE OUTLINE statement for more information on creating
an outline.
11 – OPTIMIZE_WITH
Selects one of three optimzation controls: DEFAULT (as used by
previous versions of Rdb), AGGRESSIVE (assumes smaller numbers of
rows will be selected), and SAMPLED (which uses literals in the
query to perform preliminary estimation on indices).
The following example shows how to use this clause.
SQL> select * from employees where employee_id > '00200'
cont> optimize with sampled selectivity;
12 – order-by-clause
See Select_Expressions in the Oracle Rdb SQL Reference Manual for
a description of the ORDER BY expression.
13 – PLACEMENT_ONLY_RETURNING
Syntax options:
PLACEMENT ONLY RETURNING DBKEY | PLACEMENT ONLY RETURNING ROWID
Returns the dbkey of a specified record, but does not insert
any actual data. The PLACEMENT ONLY RETURNING DBKEY clause lets
you determine the target page number for records that are to
be loaded into the database. When you use this clause, only the
area and page numbers from the dbkeys are returned. Use of this
clause can improve bulk data loads. If you use the PLACEMENT ONLY
clause, you can return only the dbkey values. Use the PLACEMENT
ONLY RETURNING DBKEY clause only in programs that load data into
an existing database and only with rows placed via a hashed index
in the storage map. For more information, see the Oracle Rdb
Guide to Database Design and Definition.
The keyword ROWID is a synonym to the DBKEY keyword.
14 – RETURNING value expr
Returns the value of the column specified in the values list. If
DBKEY or ROWID is specified, this argument returns the database
key (dbkey) of the row being added. (The ROWID keyword is a
synonym to the DBKEY keyword.) When the DBKEY value is valid,
subsequent queries can use the DBKEY value to access the row
directly.
The RETURNING DBKEY clause is not valid in an INSERT statement
used to assign values to the segments in a column of the LIST OF
BYTE VARYING data type.
15 – select-clause
See Select_Expressions in the Oracle Rdb SQL Reference Manual for
a description of the SELECT expression.
16 – select-expr
Specifies a select expression that specifies a result table.
The result table can contain zero or more rows. All the rows of
the result table are added to the target table named in the INTO
clause.
This is the only situation supported in SQL that allows you to
specify a second database in a single SQL statement.
The number of columns in the result table must correspond to the
number of columns specified in the list of column names. If you
did not specify a list of column names, the number of columns in
the result table must be the same as the number of columns in the
target table. For each row of the result table, the value of the
first column is assigned to the first column of the target table,
the second value to the second column, and so on.
You cannot specify a select expression in an INSERT statement
used to assign values to the segments in a column of the LIST OF
BYTE VARYING data type.
For detailed information on select expressions, see the Select_
Expressions HELP topic.
17 – VALUES value expr
Specifies a list of values to be added to the table as a single
row. The values can be specified through parameters, qualified
parameters, column select expressions, value expressions, or the
default values.
The values listed in the VALUES argument can be selected from
another table, but both tables must reside in the same database.
The number of values in the list must correspond to the number
of columns specified in the list of column names. If you did not
specify a column list, the number of values in the list must be
the same as the number of columns in the table. The first value
specified in the list is assigned to the first column, the second
value to the second column, and so on.
Values for IDENTITY, COMPUTED BY, and AUTOMATIC COLUMNS are not
able to be inserted so these column types are not considered for
the default column list.
See the SQL Online Help topic INSERT EXAMPLES for an example that
shows an INSERT statement with a column select expression.