INSERT

The INSERT command adds rows to a table. The following two forms of the INSERT command are described individually:

The form used to add rows having values you define. You can add a single row or (in an application program) you can insert multiple rows using the bulk facility. There is special syntax for prepared INSERT and BULK INSERT statements that use dynamic parameter substitution.
The form used to add rows defined by a SELECT command. This form copies rows from one or more tables or views into a table and is called a Type 2 INSERT.

Rules defined with a StatementType of INSERT will affect both forms of INSERT command.

Scope

ISQL or Application Programs

SQL Syntax - Insert Rows with Defined Values

[BULK] INSERT INTO {[Owner.] TableName [Owner.] ViewName } [({ColumnName} [,...])] VALUES ({ SingleRowValues BulkValues ? })

Parameters - Insert Rows with Defined Values

BULK

is specified in an application program to insert multiple rows with a single execution of the INSERT command.

[Owner.]TableName

identifies the table to which data is to be added.

[Owner.]ViewName

identifies a view on a single table; the data is added to the table upon which the view is based. Refer to the CREATE VIEW command for restrictions governing insertion via a view.

ColumnName

specifies a column for which values are supplied.

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.

You can omit the column name list if you provide values for all columns of the table in the same order the columns were specified in the CREATE TABLE (or CREATE VIEW) command.

VALUES

The VALUES clause specifies the values corresponding to the columns in the column name list, or the columns specified in the CREATE TABLE or CREATE VIEW commands, if no column name list exists. Character and date/time literals must be in single quotes.

SingleRowValues

defines column values when you insert a single row. The syntax for SingleRowValues is presented separately below and includes single row syntax for statements that do not use dynamic parameter substitution.

BulkValues

defines values when you use the BULK option. The syntax for BulkValues is presented separately below and includes bulk value syntax for statements that do not use dynamic parameter substitution.

?

is a dynamic parameter value that defines column values within a prepared insert statement that uses dynamic parameter substitution. The syntax for DynamicParameterValues is presented separately below and includes both single row and bulk processing for such statements.

SQL Syntax--SingleRowValues

The following syntax applies to single row inserts that do not use dynamic parameter substitution.

{ NULL USER :HostVariable [ [INDICATOR] :IndicatorVariable ] ? :LocalVariable :ProcedureParameter ::Built-inVariable ConversionFunction CurrentFunction [ + - ] { Integer Float Decimal } 'CharacterString' 0xHexadecimalString 'LongColumnIOString' } [,...]

Parameters--SingleRowValues

NULL

indicates a null value.

USER

evaluates to the current DBEUserID. In ISQL, it evaluates to the logon name of the ISQL user. From an application program, it evaluates to the logon name of the individual running the program.

USER behaves like a CHAR(20) constant, with trailing blanks if the logon name has fewer than 20 characters.

HostVariable

contains a value in an application program being input to the expression.

IndicatorVariable

names an indicator variable, whose value determines whether the associated host variable contains a NULL value:

> = 0: the value is not NULL
< 0: the value is NULL (The value in the host variable will be ignored.)




	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.

?

is a place holder for a dynamic parameter in a prepared SQL statement in an application program. The value of the dynamic parameter is supplied at run time.

LocalVariable

contains a value in a procedure.

ProcedureParameter

contains a value that is passed into or out of a procedure.

Built-inVariable

is one of the following built-in variables used for error handling:

::sqlcode
::sqlerrd2
::sqlwarn0
::sqlwarn1
::sqlwarn2
::sqlwarn6
::activexact

The first six of these have the same meaning that they have as fields in the SQLCA in application programs. Note that in procedures, sqlerrd2 returns the number of rows processed, for all host languages. However, in application programs, sqlerrd3 is used in COBOL, Fortran, and Pascal, while sqlerr2 is used in C. ::activexact indicates whether a transaction is in progress or not. For additional information, refer to the application programming guides and to the chapter "Constraints, Procedures, and Rules."

ConversionFunction

returns a value that is a conversion of a date/time data type into an INTEGER or CHAR value, or from a CHAR value.

CurrentFunction

indicates the value of the current DATE, TIME, or DATETIME function.

Integer

specifies a value of type INTEGER or SMALLINT.

Float

specifies a value of type FLOAT or REAL.

Decimal

specifies a value of type DECIMAL.

CharacterString

specifies a CHAR, VARCHAR, DATE, TIME, DATETIME, or INTERVAL value.

HexadecimalString

specifies a BINARY or VARBINARY value. If the string is shorter than the target column, it is padded with binary zeroes; if it is longer than the target column, the string is truncated.

LongColumnIOString

specifies the input and output locations for the LONG data. The specification for this string is given below.

SQL Syntax--LongColumnIOString

< { FileName [.Group [.Account] ] %HeapAddress:LengthofHeap } [{ > >> >! } { FileName [.Group [.Account] ] CharString$ CharString$CharString } >%$ ]

Parameters--LongColumnIOString

FileName[.GroupName[.AccountName] ]: is the location of the input file.
%HeapAddressValue: is the heap address where the input is located or where the output is to be located.
LengthOfHeap: is the length of the heap specified for input. The length is used only for the input portion of the string.
>: specifies that output is placed in the following file. If the file already exists, it is not overwritten nor appended to, and an error is generated.
>>: specifies that output will be appended to the following file name. If the file does not exist, it is created.
>!: specifies that output is placed in the following file name. If the file already exists, it is overwritten.
>%$: is a heap address, generated by ALLBASE/SQL, where the output is placed. This option cannot be used with a file name.
$: is the wild card character that represents a random 5 byte alphanumeric character string in file names.

Description--LongColumnIOString

The input device must not be locked or have privilege security.
An input device file can be a standard MPE/iX file with fixed record size, valid blocking factor, valid file equations, ASCII/binary option, and user labels option. Any related output device file will have the same characteristics as the input device file.
When the input device is a heap address and no output device is specified, output is a standard MPE/iX file with an 80-byte record size, a blocking factor of 1, a binary option, and a file size equal to the LONG column size.
When the input device is a file and no output device is specified, ALLBASE/SQL generates a random file name with the same characteristics as the input file specified in the LONG column I/O string. If the input file contains labels, then the output file contains the same amount of labels.
When no portion of the output device name is specified, the default file name, tmp$, is used. The wildcard character ($) indicates a random, five-byte, alphanumeric character string. This file is created in the local group.
When you specify a portion of the output file name in conjunction with the wildcard character $, a five-byte, alphanumeric character string replaces the wildcard. The wildcard character can appear in any position of the output device name except the first. The maximum file name being 8 bytes, you can specify 3 bytes of the device name, at least one of which is in the first character position of the name and is not numeric.
The wildcard character, whether user specified or part of the default output device name, is an almost unique five-byte, alphanumeric character string. The possibility of two identical wildcards being generated is remote.
When a file is used as the LONG column input or output device and you do not specify a group and account name in the LONG column I/O string, the default is the group and account in which your program is running.
The output device cannot be overwritten with a SELECT or FETCH command unless you use the INSERT or UPDATE command with the overwrite option.

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 is truncated. Refer to the "Data Types" chapter for information on overflow and truncation of other data types.
No error or warning condition is generated by ALLBASE/SQL when a character or binary string is truncated during an INSERT operation.
Using the INSERT command with views requires that the views be based on queries that are updatable. See "Updatability of Queries" in the chapter "SQL Queries."
Values in referenced (primary key) columns must be inserted before values in referencing (foreign key) columns. However, if you do a bulk insertion, inserting the primary key rows after the foreign key rows does not cause an error message, because the constraints are satisfied by the time you COMMIT WORK.
A table on which a unique constraint is defined cannot contain duplicate rows.
BINARY and VARBINARY data can be inserted in character or hexadecimal format. Character format requires single quotes and hexadecimal requires a 0x before the value.
Under the default settings for the SET DML ATOMICITY and SET CONSTRAINTS statements, integrity constraints on tables and views are enforced on a statement level basis and if a constraint should be violated during processing of the insert, no rows are inserted. However, the SET DML ATOMICITY and SET CONSTRAINTS statements both override the default behavior. For more information, it is important that you refer to the section "Error Conditions in ALLBASE/SQL" in the "Introduction" chapter, and the SET DML ATOMICITY or the SET CONSTRAINTS statements in this chapter.
Rows being inserted must not cause the search condition of the table check constraint to be false and must cause the search condition of the view check constraint to be true.
Rows being inserted in the table through a view having a WITH CHECK OPTION must satisfy the check constraint of the view and any underlying views in addition to satisfying any constraints of the table. Refer to the "Check Constraints" section in the "Constraints, Procedures, and Rules" chapter for more information on check constraints.
Rules defined with a StatementType of INSERT will affect all kinds of INSERT statements performed on the rules' target tables. When the INSERT is performed, ALLBASE/SQL considers all the rules defined for that table with the INSERT StatementType. If the rule has no condition, it will fire for all rows affected by the statement and invoke its associated procedure with the specified parameters on each row. If the rule has a condition, it will evaluate the condition on each row. The rule will fire on rows for which the condition evaluates to TRUE and invoke the associated procedure with the specified parameters for each row. Invoking the procedure could cause other rules, and thus other procedures, to be invoked if statements within the procedure trigger other rules.
If a DISABLE RULES statement is in effect, the INSERT statement will not fire any otherwise applicable rules. When a subsequent ENABLE RULES is issued, applicable rules will fire again, but only for subsequent INSERT statements, not for those rows processed when rule firing was disabled.
In a rule defined with a StatementType of INSERT, any column reference in the Condition or any ParameterValue will refer to the value of the column as it is assigned in the INSERT statement, or by the default value of the column if it is not included in the INSERT statement.
When a rule is fired by this statement, the rule's procedure is invoked after the changes have been made to the database for that row and all previous rows. The rule's procedure, and any chained rules, will thus see the state of the database with the current partial execution of the statement.
If an error occurs during processing of any rule considered during execution of this statement (including execution of any procedure invoked due to a rule firing), the statement and any procedures invoked by any rules will have no effect. Nothing will have been altered in the DBEnvironment as a result of this statement or the rules it fired. Error messages are returned in the normal way.
The BULK option is not allowed within a procedure.

SQL Syntax--DynamicParameterValues

The following syntax applies to single row and bulk inserts that use dynamic parameter substitution.

(? [,...] )

Parameters--DynamicParameterValues

(? [,...])

represents one or more host variables in a prepared INSERT statement. Each ? corresponds in sequential order to a column in the column name list of the prepared statement (even when BULK is used).

When you use a data structure of sqlda_type to pass dynamic parameter information between the application and ALLBASE/SQL, the number of "?"s specified must match the sqld field of the descriptor area and the number of values in a single element of the data buffer.

When you use host variables to pass dynamic parameter data values between the application and ALLBASE/SQL, the number of "?"s specified must match the number and order of the host variables in the related EXECUTE statement. This does not apply when you use the BULK option as you cannot mix host variables and dynamic parameters.

Description--Insert Rows with DynamicParameterValues

Statements using question marks (?) indicating dynamic parameters can be intermixed with items in SingleRowValues and they can return either a value or a format. When using dynamic parameters for values, the dynamic parameter becomes the data type of the column. When using dynamic parameters for conversion functions, they become the data type to which they are assigned (CHAR 72). Only TO_DATE, TO_TIME, TO_DATETIME, and TO_INTERVAL are allowed here; TO_CHAR and TO_INTEGER are not allowed.
When using the BULK option, statements using question marks (?), indicating dynamic parameters, can contain only question marks (and no host variables) to indicate column input.
The BULK option used with host variables is available for C, COBOL, and FORTRAN applications.
The BULK option used with an sqlda_type data structure is available for C and Pascal applications.
A detailed description of how to use this statement with dynamic parameters is found in the ALLBASE/SQL Advanced Application Programming Guide.

Authorization--Insert Rows with SingleRowValues and Bulk Values

If you specify the name of a table, you must have INSERT or OWNER authority for that table or you must have DBA authority.

If you specify the name of a view, you must have INSERT or OWNER authority for that view or you must have DBA authority. Also, the owner of the view must have INSERT or OWNER authority with respect to the view's base tables, or the owner must have DBA authority.

SQL Syntax--INSERT Rows Defined by a SELECT Command (Type 2 Insert)

INSERT INTO {[Owner.] TableName [Owner.] ViewName } [(ColumnName [,...])] QueryExpression

Parameters--INSERT Rows Defined by a SELECT Command (Type 2 Insert)

[Owner.]TableName

identifies the table to which data is to be added.

[Owner.]ViewName

identifies a view on a single table; the data is added to the table upon which the view is based. Refer to the CREATE VIEW command for restrictions governing inserts via a view.

ColumnName

specifies a column for which data is supplied from the select list in the SELECT command. Each column named must have a corresponding select list item. You can omit the column name list if you provide a select list item for all columns in the target table in the same order the columns were specified in the CREATE TABLE (or CREATE VIEW) command.

QueryExpression

defines the rows to be inserted based on one or more tables and/or views in the DBEnvironment. The name of the target table cannot appear within the FROM clause or in a FROM clause of any subquery. The query expression cannot contain an INTO clause or a union operation.

The data types of each column in the select list must be compatible with the data types of corresponding columns in the target table. The first select list item defines the first column in the target table, the second select list item defines the second column in the target table, and so forth. The number of select list items must equal the number of columns in the target table.

Any column in the target table can contain null values only if it was not defined with the NOT NULL attribute. Therefore ensure either that select list items are not null for any NOT NULL target column, or that the NOT NULL target columns have default values defined for them.

Description--INSERT Rows Defined by a SELECT Command (Type 2 Insert)

You cannot use the ORDER BY clause in a Type 2 Insert.
You cannot insert into a LONG column with this kind of INSERT operation.
You cannot specify a LONG column in the QueryExpression in this kind of INSERT operation, except in a long column or string function.
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 this kind of INSERT operation, 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.
Using the INSERT command with views requires that the views be based on updatable queries. See "Updatability of Queries" in the "SQL Queries" chapter.
A table on which a unique constraint is defined cannot contain duplicate rows.
Under the default settings for the SET DML ATOMICITY and SET CONSTRAINTS statements, integrity constraints on tables and views are enforced on a statement level basis and if a constraint should be violated during processing of the insert, no rows are inserted. However, the SET DML ATOMICITY and SET CONSTRAINTS statements both override the default behavior. For more information, it is important that you refer to the section "Error Conditions in ALLBASE/SQL" in the "Introduction" chapter, and the SET DML ATOMICITY or the SET CONSTRAINTS statements in this chapter.
Rows being inserted must not cause the search condition of the table check constraint to be false and must cause the search condition of the view check constraint to be true.
Rows being inserted in the table through a view having a WITH CHECK OPTION must satisfy the check constraint of the view and any underlying views in addition to satisfying any constraints of the table. Refer to the "Check Constraints" section of the "Constraints, Procedures, and Rules" chapter for more information on check constraints.
Values in referenced (primary key) columns must be inserted before values in referencing (foreign key) columns. However, if you do a bulk insertion, inserting the primary key rows after the foreign key rows does not cause an error message, as the constraints are satisfied by the time you COMMIT WORK.
BINARY and VARBINARY data can be inserted in character or hexadecimal format. Character format requires single quotes and hexadecimal requires a 0x before the value.
Rules defined with a StatementType of INSERT will affect all kinds of INSERT statements performed on the rules' target tables. When the INSERT is performed, ALLBASE/SQL considers all the rules defined for that table with the INSERT StatementType. If the rule has no condition, it will fire for all rows affected by the statement and invoke its associated procedure with the specified parameters on each row. If the rule has a condition, it will evaluate the condition on each row. The rule will fire on rows for which the condition evaluates to TRUE and invoke the associated procedure with the specified parameters for each row. Invoking the procedure could cause other rules, and thus other procedures, to be invoked if statements within the procedure trigger other rules.
If a DISABLE RULES statement is in effect, the INSERT statement will not fire any otherwise applicable rules. When a subsequent ENABLE RULES is issued, applicable rules will fire again, but only for subsequent INSERT statements, not for those rows processed when rule firing was disabled.
In a rule defined with a StatementType of INSERT, any column reference in the Condition or any ParameterValue will refer to the value of the column as it is assigned in the INSERT statement, or by the default value of the column if it is not included in the INSERT statement.
The set of rows to be inserted by a type 2 INSERT (that is, an INSERT defined by a SELECT statement) is determined before any rule fires, and this set remains fixed until the completion of the rule. In other words, if the rule adds to, deletes from, or modifies this set, such changes are ignored.
When a rule is fired by this statement, the rule's procedure is invoked after the changes have been made to the database for that row and all previous rows. The rule's procedure, and any chained rules, will thus see the state of the database with the current partial execution of the statement.
If an error occurs during processing of any rule considered during execution of this statement (including execution of any procedure invoked due to a rule firing), the statement and any procedures invoked by any rules will have no effect. Nothing will have been altered in the DBEnvironment as a result of this statement or the rules it fired. Error messages are returned in the normal way.

Authorization--INSERT Rows Defined by a SELECT Command (Type 2 Insert)

To insert rows into a table, you must have INSERT or OWNER authority for that table or you must have DBA authority.

To insert rows using a view, you must have INSERT or OWNER authority for that view or you must have DBA authority. Also, the owner of the view must have INSERT or OWNER authority with respect to the view's base tables, or the owner must have DBA authority.

If you specify the name of a table in the FROM clause of the SELECT command, you must have SELECT or OWNER authority for the table or you must have DBA authority. If you specify the name of a view in the FROM clause of the SELECT command, you must have SELECT or OWNER authority for the view or you must have DBA authority. Also, the owner of the view must have SELECT or OWNER authority with respect to the view's definition, or the owner must have DBA authority.

Examples

Single-row insert

   INSERT INTO PurchDB.Vendors

     VALUES  (9016,

             'Secure Systems, Inc.',

             'John Secret',

             '454-255-2087',

             '1111 Encryption Way',

             'Hush',

             'MD',

             '00007',

             'discount rates are carefully guarded secrets')

A new row is added to the PurchDB.Vendors table.

Bulk insert
BULK INSERT INTO PurchDB.Parts (PartNumber, PartName) VALUES (:NewRow, :Indx, :NumRow)
Programmatically, you can insert multiple rows with one execution of the INSERT command if you specify the BULK option. In this example, the rows to be inserted are in the array called NewRow.

Insert using SELECT operation

   CREATE PUBLIC TABLE PurchDB.CalifVendors

     (VendorName        CHAR(30)       NOT NULL,

      PartNumber        CHAR(16)       NOT NULL,

      UnitPrice         DECIMAL(10,2),

      DeliveryDays      SMALLINT,

      VendorRemarks     VARCHAR(60)   )

  IN PurchFS

This table has the same column attributes as corresponding columns in PurchDB.SupplyPrice and PurchDB.Vendors.

   INSERT INTO PurchDB.CalifVendors

     SELECT  VendorName, PartNumber, UnitPrice, DeliveryDays, VendorRemarks

       FROM  PurchDB.Supplyprice, PurchDB.Vendors

       WHERE PurchDB.SupplyPrice.VendorNumber = PurchDB.Vendors.VendorNumber

         AND VendorState = 'CA'

Rows for California vendors are inserted based on a query result obtained by joining PurchDB.SupplyPrice and PurchDB.Vendors. A column list is omitted because all columns in the target table have a corresponding select list item.

Single row insert using dynamic parameters with host variables
PREPARE CMD FROM 'INSERT INTO PurchDB.Parts (PartNumber, PartName) VALUES(?,?);'
A new row is added to the PurchDB.Parts table based on the prepared INSERT statement called CMD. Row values are provided at run time, and an EXECUTE statement using two host variables is required to complete the INSERT.
EXECUTE CMD USING :PartNumber, :PartName;
Bulk insert using dynamic parameters with host variables
PREPARE CMD FROM 'BULK INSERT INTO PurchDB.Parts (PartNumber, PartName) VALUES(?,?);'
Multiple rows can be added to the PurchDB.Parts table. Row values are provided at run time, and an EXECUTE statement using the address of a host variable array containing dynamic parameter data and host variables containing the starting index and number of rows to be inserted complete the INSERT.
EXECUTE CMD USING :DataBuffer, :StartIndex, :NumberOfRows;
Bulk insert or single row insert using dynamic parameters with sqlda_type and related data structures
PREPARE CMD FROM 'BULK INSERT INTO PurchDB.Parts (PartNumber, PartName) VALUES(?,?);'
One or more rows can be added to the PurchDB.Parts table. Row values are provided at run time, and an EXECUTE statement using a descriptor area is required to complete the INSERT.
Before issuing the execute statement, you must set certain fields in the descriptor area. (The ALLBASE/SQL application programming guides contain detailed information regarding this technique.) Then you describe the input to ALLBASE/SQL.
DESCRIBE INPUT CMD INTO Sqlda; EXECUTE CMD USING DESCRIPTOR Sqlda;