SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-147
INSERT Statement
(target-col-list)
names the columns target-col-list in the table or view in which to insert
values. The data type of each target column must be compatible with the data type
of its corresponding source value. Within the list, each target column must have the
same position as its associated source value, whose position is determined by the
columns in the table derived from the evaluation of the query expression
(query-expr).
If you do not specify all of the columns in table in the target-col-list,
column default values are inserted into the columns that do not appear in the list.
See Column Default Settings on page 6-7.
If you do not specify target-col-list, row values from the source table are
inserted into all columns in table (with the exception of a SYSKEY column). The
order of the column values in the source table must be the same order as that of
the columns specified in the CREATE TABLE for table. (This order is the same
as that of the columns listed in the result table of SELECT * FROM table.)
insert-source
specifies the rows of values insert-source to be inserted into all columns of
table or, optionally, into specified columns of table.
query-expr
specifies the query expression that generates the source table consisting of
rows of values to be inserted into the columns named in target-col-list, if
specified, or into all the columns of table by default. If there are no rows
returned in insert-source, no rows are inserted into table. If
query-expr is not a VALUES clause, the insert-source cannot reference
either table or any view based on table, or any base table or view on which
table is based.
The number of columns in the column list (or by default the number of columns
in table) must be equal to the number of columns in the source table derived
from the evaluation of the query expression. Further, the data type of each
column in the column list (or by default each column in table) must be
compatible with the data type of its corresponding column in the source table.
A single value within a VALUES clause can be a value expression, NULL, or
DEFAULT. If you specify DEFAULT within a VALUES clause, the value inserted
is the DEFAULT value defined for the target column. A value expression can
also include DEFAULT as an operand; the value inserted is the expression
evaluated with the DEFAULT value. For example, DEFAULT + 50 can be an
expression in a row value constructor.
The use of DEFAULT in a value expression is an SQL/MX extension.
If you attempt to insert NULL into a column that is defined as NOT NULL or
DEFAULT into a column that is defined with NO DEFAULT, NonStop SQL/MX
returns an error.