SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
I-17
Considerations—INSERT

Date and time values
A date-time data type is compatible only with another date-time data type with
the same range of DATETIME fields.
When you use a range of fields to specify only some of the DATETIME fields
for a DATETIME column, SQL uses the current date and time for any missing
fields to the left of the fields for which values are specified. For missing fields to
the right of the fields for which values are specified, SQL uses these values:
YEAR -- Current year HOUR -- 00
MONTH -- 01 MINUTE -- 00
DAY -- 01 SECOND -- 00
FRACTION -- 000000

SYSKEY values
For a table with relative organization, the value of a SYSKEY cannot exceed
4294963199 and cannot be greater than the maximum number of rows the
table can contain. For a table with key-sequenced organization, the value of a
SYSKEY cannot exceed 2**63 minus 1.
If you insert rows into a protection view defined with a WHERE clause that
refers to the SYSKEY column, you cannot specify APPEND or ANYWHERE
and you must include SYSKEY in the column list. If such a protection view is
also based on a key-sequenced or entry-sequenced table, you cannot insert a
row into the view.
In addition, values in each row inserted must satisfy any constraints on the
table or on the underlying table of the view. (A table constraint is satisfied if the
check condition is not false - that is, it is either true or has an unknown value.)
If the view is defined with WITH CHECK OPTION, the row must satisfy the
view selection criteria specified in the WHERE clause of the AS select-stmt
clause in the CREATE VIEW statement.
If a row does not qualify, SQL stops inserting rows and returns an error
message.

Use the CAST function to change the data types for input values specified in an
INSERT statement. If a SELECT operation is specified in an INSERT statement,
rather than input values, the CAST function must be used on the SELECT query
output columns. For more information, see CAST Function on page C-4.

To insert a null value, use the keyword NULL. From a program, you can also use
an indicator variable to insert a null value, as described in the SQL/MP
programming manual for your host language.

To allow VSBB for insert operations for a nonaudited file, use the CONTROL
TABLE directive with SEQUENTIAL INSERT ON, SYNCDEPTH 0, and TABLE
LOCK ON options. Additionally, specify FOR REPEATABLE ACCESS in your
INSERT statement and specify IN EXCLUSIVE MODE in the LOCK TABLE
statement.