Neoview SQL Reference Manual (R2.5)

to form a new row. If you specify DEFAULT VALUES, you cannot specify a column list.
You can use DEFAULT VALUES only when all columns in table have default values.
Considerations for INSERT
Authorization Requirements
INSERT requires authority to read and write to the table or view receiving the data and authority
to read tables or views specified in the query expression (or any of its subqueries) in the INSERT
statement.
Transaction Initiation and Termination
The INSERT statement automatically initiates a transaction if no transaction is active. Alternatively,
you can explicitly initiate a transaction with the BEGIN WORK statement. After a transaction is
started, the SQL statements execute within that transaction until a COMMIT or ROLLBACK is
encountered or an error occurs. If AUTOCOMMIT is ON, the transaction terminates at the end
of the INSERT statement.
Self-Referencing INSERT and BEGIN WORK or AUTOCOMMIT OFF
A self-referencing INSERT statement is one that references, in the statement's insert-source,
the same table or view into which rows will be inserted (see “Examples of Self-Referencing
Inserts” (page 148)). A self-referencing INSERT statement will not execute correctly and an error
is raised if either BEGIN WORK or AUTOCOMMIT OFF is used unless the compiler's plan sorts
the rows before they are inserted. If you want to use a self-referencing INSERT statement, you
should avoid the use of BEGIN WORK or AUTOCOMMIT OFF. For information about
AUTOCOMMIT, see the “SET TRANSACTION Statement” (page 188).
Isolation Levels of Transactions and Access Options of Statements
The isolation level of a Neoview SQL transaction defines the degree to which the operations on
data within that transaction are affected by operations of concurrent transactions. When you
specify access options for the DML statements within a transaction, you override the isolation
level of the containing transaction. Each statement then executes with its individual access option.
You can explicitly set the isolation level of a transaction with the SET TRANSACTION statement.
See “SET TRANSACTION Statement” (page 188).
Use of a VALUES Clause for the Source Query Expression
If the query expression consists of the VALUES keyword followed by rows of values, each row
consists of a list of value expressions or a row subquery (a subquery that returns a single row of
column values). A value in a row can also be a scalar subquery (a subquery that returns a single
row consisting of a single column value).
Within a VALUES clause, the operands of a value expression can be numeric, string, datetime,
or interval values; however, an operand cannot reference a column (except in the case of a scalar
or row subquery returning a value or values in its result table).
Requirements for Inserted Rows
Each row to be inserted must satisfy the constraints of the table or underlying base table of the
view. A table constraint is satisfied if the check condition is not false—it is either true or has an
unknown value.
Using Compatible Data Types
To insert a row, you must provide a value for each column in the table that has no default value.
The data types of the values in each row to be inserted must be compatible with the data types
of the corresponding target columns.
INSERT Statement 145