INSERT statement Format 3, Format 4 (Insert row using an array)

Function

Can insert rows when an embedded variable of the array format is specified.

Format 3
Inserts rows into a table by column.
Format 4
Treats an entire row as a single data item and inserts rows into a table of the FIX attribute row by row.

Privileges

A user who has the INSERT privilege for a table can insert rows into that table.

However, if a query is specified in the INSERT statement, the user needs the SELECT privilege for the table for which the query is specified.

Format 3: Inserting multiple rows by column into a table by specifying an embedded variable array

FOR : embedded-variable
INSERT INTO [[RD-node-name.]authorization-identifier.] table-identifier
   [(column-name [, column-name]...)]
     {VALUES (insertion-value[, insertion-value]...)
     | query-expression-body}
   [WITH ROLLBACK]

query-expression-body:: = {query-specification
          | (query-expression-body)
          | query-expression-body {UNION|EXCEPT} [ALL]
           {query-specification | (query-expression-body)}}
query-specification :: = SELECT [{ALL|DISTINCT}] {selection-expression[, selection-expression]... | * } table-expression
table-expression    :: = FROM table-reference[, table-reference]...
           [WHERE search-condition]
           [GROUP BY value-expression[, value-expression]...]
           [HAVING search-condition]

Format 4: Inserting multiple rows by row into a table with the FIX attribute by specifying an embedded variable array

FOR : embedded-variable
INSERT INTO [[RD-node-name.]authorization-identifier.]table-identifier (ROW)
  {VALUES (:embedded-variable-array [:indicator-variable-array])
  | query-expression-body }
  [WITH ROLLBACK]

query-expression-body:: = {query-specification
          | (query-expression-body)
          | query-expression-body {UNION|EXCEPT} [ALL]
           {query-specification | (query-expression-body)}}
query-specification :: = SELECT [{ALL|DISTINCT}] {selection-expression[, selection-expression]... | * } table-expression
selection-expression    :: = FROM table-reference[, table-reference]...
           [WHERE search-condition]
           [GROUP BY value-expression [, value-expression]...]
           [HAVING search-condition]

Operands

For details about operands other than FOR, VALUES, or ROW, see INSERT statement Format 1 (Insert row).

Format 3 operands

insertion-value
Inserts corresponding insertion values into the columns specified in column-name. The following items can be specified in this operand:
  • : embedded-variable-array [: indicator-variable-array]
  • Value expression
  • NULL (represents the null value)
  • DEFAULT
However, embedded variables not in the array format cannot be specified in insertion-value.

Format 4 operands

Specify this operand when inserting data by row. Specification of the ROW operand is subject to the following rules:

  1. The ROW operand can be specified only for base tables with the FIX attribute. ROW refers to an entire row. Specifying ROW causes HiRDB to treat the entire row as a single data item and insert it from one area. The data type of the data to be inserted should be the ROW type, irrespective of the data types of columns (for the ROW type, a variable corresponding to CHAR (n) [where n is the row length] or a structure of the same length can be specified, provided that the structure does not contain a boundary alignment gap). The data length should be equal to the row length (the sum of the data lengths of the columns).
  2. The platform on which the UAP runs and the platform on which the HiRDB server runs should have the same endian. The ROW option cannot be used between different endians. For example, if ROW is used in a Windows version UAP, the HiRDB server should also use the Windows version of the same endian.

Operands common to Formats 3 and 4

Specifies the embedded variable storing the number of rows to be inserted using an embedded variable array. An embedded variable of the SMALLINT type should be specified. The allowable range of values is from 1 to 4,096, less than or equal to the number of elements in the embedded variable array or in the indicator variable array. Only non-zero, positive values are allowed. An out of range value can cause a run-time error.

embedded-variable-array
Specifies the embedded variable that was declared in the array format. Specify an array variable for inserting non-null values.
The values to be inserted into the rows should be assigned to the elements of the embedded variable array. If the values to be inserted include the NULL value, specify both an embedded variable array and an indicator variable array.
indicator-variable-array
Specifies the indicator variable that was declared in the array format. Values indicating that the values to be inserted into the elements of the embedded variable array are not the NULL value should be assigned to the corresponding elements in the indicator variable array. For allowable values, see 1.5.5 Setting a value for an indicator variable. Notice that the NULL value cannot be inserted into a base table with the FIX attribute.

Common rules

Common rules on Format 3

  1. The data type of the embedded variable array should be either the data type of the corresponding column or a convertible data type.
  2. The number of columns per row to be inserted should be equal to the number of columns specified in column-name. Their values should have the data type of the columns or a convertible data type.
  3. The following data types are not allowed in an INSERT statement using an array: BLOB type, BINARY type with a maximum length of 32,001 bytes or greater, and the abstract data type.
  4. In an INSERT statement using an array, data cannot be inserted into a repetition column.
  5. When fixed-point or floating-point data is inserted into a column of any of the following data types, the fractional part (below the decimal point) is truncated:
    • INTEGER
    • SMALLINT
    Also, when fixed-point data is inserted into a DECIMAL type column, any digits below the scaling for the column are truncated.
  6. Character data longer than the length that was specified when the table was defined cannot be inserted into the table.
  7. Numerical data outside the range of the data type defined for the column cannot be inserted into the column.
  8. If the data inserted into a column of a fixed-length character string (including national character strings and mixed character strings) is shorter than the column length, the data is inserted left-justified, and the remainder of the column is blank-filled.

Common rules for Format 3 and Format 4

  1. One or more embedded variable arrays should be specified in a clause other than the FOR clause. A failure to specify an embedded variable array can cause an error.
  2. Specifying an embedded variable not in the array format in a clause other than the FOR clause can cause an error.
  3. The number of elements in the embedded variable array or indicator variable array should be in the 1 to 4,096 range. An out of range value can cause an error. The number of elements should be greater than or equal to the maximum number of rows specified in FOR:embedded-variable.
  4. Because it includes embedded variable arrays and indicator variable arrays, the INSERT statement Format 3 cannot be preprocessed by the PREPARE statement. For dynamic execution of the INSERT statement Format 3, see EXECUTE statement Format 2 (Execute an SQL statement using an array).
  5. An INSERT statement using an array cannot be specified in a procedure.
  6. If a warning-generating event occurs in any of the rows to be inserted, warning information is assigned to the SQLWARN flag of the SQL communications area.
  7. If an error occurs in any of the rows to be inserted, the transaction is rolled back.
  8. If the INSERT statement is executed on a table with a WITHOUT ROLLBACK specification, the timing for the release of row locking can vary depending on whether an index is defined. For details, see the rules on WITHOUT ROLLBACK in CREATE TABLE (Define table) in Chapter 3.
  9. Before inserting data into a shared table, the LOCK statement with respect to the table should be executed in the lock mode. An attempt to insert data into a shared table without executing the LOCK statement can cause an error. For details about updating a shared table, see the HiRDB Version 8 Installation and Design Guide. For the objects of locking during execution of the LOCK statement on a shared table, see the notes in LOCK statement (Lock control on tables) in Chapter 5.

Rules on referential constraints

  1. For rules on inserting referential constraints by embedded variable array or by row, see the rules on referential constraints in INSERT statement Format 1 (Insert row).
  2. For the insertion of rows into a referenced table, the referencing table is referenced to determine whether the value of the foreign key constituent column is included in the value of the primary key constituent column in the referenced table. The data guarantee level during the search through the referenced table assumes the share mode. For this reason, if during the insertion of rows into the referencing table, operations are performed on the referenced table by another transaction, the row insertion operation goes into a wait state until the transaction is settled.

Notes

  1. When row insertion is executed on a foreign table, insertion using an array is not carried out between the local HiRDB and a foreign server. The insertion operation is repeated the number of times specified in the embedded variable to insert rows.

Examples

  1. Batch-insert data equivalent to 50 rows, assigned to an array variable in the C language, into an inventory table:

    EXEC SQL BEGIN DECLARE SECTION;
     short   XINSERT_NUM;
     long    XPCODE[50];
     short   IPCODE[50];
     char    XPNAME[50][17];
     short   IPNAME[50];
    EXEC SQL END DECLARE SECTION;
               :
    Assign values to the elements of the variable array
               :
    XINSERT_NUM = 50;
    EXEC SQL FOR :XINSERT_NUM
       INSERT INTO STOCK(PCODE,PNAME)
         VALUES (:XPCODE:IPCODE,:XPNAME:IPNAME);

  2. Insert the value of the entire row assigned to an array variable in the C language into the inventory table (STOCK) in a single operation covering 50 rows:

    EXEC SQL BEGIN DECLARE SECTION;
     short   XINSERT_NUM;
     char    XROWS[50][31];
    EXEC  SQL  END  DECLARE  SECTION;
                :
    Assign values to the elements of the variable array
                :
    XINSERT_NUM = 50;
    EXEC  SQL  FOR :XINSERT_NUM
       INSERT INTO STOCK(ROW) VALUES(:XROWS);