ALLBASE/SQL Reference Manual (36216-90216)

450 Chapter11
SQL Statements E - R
INSERT
SQL Syntax — BulkValues
The following syntax applies only to statements that do not use dynamic parameter
substitution.
: Buffer [,: StartIndex [,: NumberOfRows ]]
Parameters — BulkValues
Buffer is a host array or structure containing rows that are the input for the
INSERT command. This array contains elements for each column to be
inserted and indicator variables for columns that can contain null values.
Whenever a column can contain nulls, an indicator variable must be
included in the array definition immediately after the definition of that
column. This indicator variable is an integer that can have the following
values:
> = 0 the value is not NULL
< 0 the value is NULL
NOTE To be consistent with the standard SQL and to support portability of code, it
is strongly recommended that you use a −1 to indicate a null value. However,
ALLBASE/SQL interprets all negative indicator variable values to mean a
null value.
StartIndex is a host variable whose value specifies the array subscript denoting where
the first row to be inserted is stored in the array; default is the first
element of the array.
NumberOfRows is a host variable whose value specifies the number of rows to insert;
default is to insert from the starting index to the end of the array.
Description — Insert Rows with SingleRowValues and BulkValues
• When you enter SQL commands interactively, you cannot use host variables or the
BULK option.
• You cannot use the BULK option in a procedure.
• If you omit any of the table's columns from the column name list, the INSERT command
places the default value of the respective column definitions in the omitted columns. For
columns with no default value, the null value is placed in the omitted columns. If the
table definition specifies NOT NULL for any of the omitted columns, the INSERT
command fails.
• If ALLBASE/SQL detects an error during a BULK INSERT operation, the error handling
behavior is determined by the setting of the SET DML ATOMICITY and SET
CONSTRAINTS statements. Refer to the discussion of these statements in this chapter
for more information.
• For CHAR and VARCHAR data, if a
CharacterString literal is shorter than the
target column, it is padded with blanks; if it is longer than the target column, the string