INSERT

• 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.

Scope

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

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

Parameters — SingleRowValues

NULL

indicates a null value.

USER

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

USER behaves like a CHAR(20) constant, with trailing blanks if the login 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 Chapter 4 "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

  <{[PathName/]FileName
    %SharedMemoryAddress}
  [{>
    >>
    >!}[PathName/]{FileName
                   CharSting$
                   CharString$ CharString}
    >%{SharedMemoryAddress
       $                   }               ]

Parameters — LongColumnIOString

< [PathName/] FileName

is the location of the input file.

<% SharedMemoryAddress

is the shared memory address where the input is located.

>

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 is 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.

>% SharedMemoryAddress

is the shared memory address where the output is placed.

>%$

is the shared memory address, determined by ALLBASE/SQL, where the output is placed.

$

is the wildcard character that represents a random, five-byte alphanumeric character string generated by ALLBASE/SQL. This is a file name.

Description — LongColumnIOString

• The input device must have a permission allowing the login user to access it. For example, if the file belongs to the login user, permission must be at least 400. If the file belongs to another user, in a different group, permission must be at least 004.

• When an output device has been specified and it exists prior to a SELECT or FETCH command, ALLBASE/SQL does not change the file's owner or permission.

• The output device, if it does not exist prior to a SELECT or FETCH command, is created with the following characteristics.

  Table 11-5 Default Output Device Characteristics

  Device Type	Permission	UserID (uid)	GroupID (gid)
  OUTPUT create	700	Current user login id	Current user login group
  OUTPUT append	200	Current user login id	Current user login group
  OUTPUT overwrite	200	Current user login id	Current user login group

• If the output device exists prior to a SELECT or FETCH command, in order for ALLBASE/SQL to access it for append or overwrite, the above characteristics are recommended.

• When no portion of the output device name is specified, the default file name, tmp$.LF, is used. The wildcard character ($) indicates a random, five-byte, alphanumeric character string. This file is created in the local directory.

• 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 14 bytes, you can specify 9 bytes of the device name.

• The wildcard character, whether user specified or part of the default output device name, is a unique five-byte, alphanumeric character string.

• When a file is used as the LONG column input or output device and you do not give it a specific path name in the LONG column I/O string, the default is the path where ISQL or 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.

• LONG columns cannot be used as follows:

  • In a WHERE clause.

  • In a type II INSERT command.

  • Remotely through ALLBASE/NET.

  • As hash or B-tree index key columns.

  • In a GROUP BY, ORDER BY, DISTINCT, or UNION clause.

  • In an expression.

  • In a subquery.

  • In aggregate functions (AVG, SUM, MIN, MAX).

  • As columns to which integrity constraints are assigned.

  • With the DEFAULT option of the CREATE or ALTER TABLE commands.

SQL Syntax — BulkValues

  :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 Chapter 7 "Data Types" 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 Chapter 3 "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 Chapter 1 "Introduction" 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 Chapter 4 "Constraints, Procedures, and Rules" 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

  (? [,...])

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.

Authorization —
Insert Rows with SingleRowValues and Bulk Values

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 Chapter 3 "SQL Queries"

• 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 Chapter 1 "Introduction" 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 Chapter 4 "Constraints, Procedures, and Rules" 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)

Examples

1. 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.

2. 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.

3. 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.

4. 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;
   
   

5. 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;
   
   

6. 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;