INSERT statement Format 2 (Insert row)

Function

Treats an entire row as a data item and inserts rows in units of rows into a FIX-attribute table. Can insert a single row through the direct specification of a value. In addition, this command can also insert one or more rows by using a query expression body.

Privileges (Format 2)

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 2: Inserting one row into a table with the FIX attribute on a row-by-row basis, by treating the entire row as a set of data

INSERT INTO [[RD-node-name.]authorization-identifier.]table-identifier|(ROW)
    {VALUES (row-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]

Operands

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

Specifies that data is to be inserted on a row-by-row basis. The following rules apply to the specifying of ROW:

  1. The ROW operand can be specified only for a base table with the FIX attribute. The operand ROW refers to an entire row. When ROW is specified, the system treats the entire row as a set of data and inserts data from one area. The data type of the data that is inserted should be the ROW type, regardless of the data types of the individual columns. (Variables corresponding to CHAR(n) (where n is the row length) or structures of the same length can be specified as ROW-type data; if a structure is specified, the structure should not contain any boundary alignment blanks.) 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.
row-insertion-values
Specifies the values to be inserted into the row corresponding to ROW. Any of the following items can be specified:
  • Embedded variables (and indicator variables)
  • ? parameters
  • SQL variables or SQL parameters

Specifies the query expression body that fetches the data to be inserted. For details about query expression bodies, see 2.2 Query expressions.

For details about query specifications, see 2.3 Query specification.

For table expressions, see 2.5 Table expressions. For table referencing, see 2.6 Table reference. For search conditions, see 2.7 Search conditions.

Notes

  1. If the data type of a table column to be inserted is DECIMAL, or a national character string, HiRDB checks the contents of the applicable row insertion values.
  2. For retrieving or updating rows (with a ROW specification), the portions that are of the date data type with respect to ROW in an embedded variable, SQL variable, or SQL parameter have a length of 4 bytes, and must be in X'YYYYMMDD' format.
    When date data is received in a predefined character string representation by using a by-row (ROW specification) interface, during the definition of a column, define the column as CHAR(10) rather than a date data type. Any date arithmetic operations should be specified using the DATE scalar function after the data involved has been converted into the date data type.
  3. For retrieving or updating rows (with a ROW specification), the portions that are of the time data type with respect to ROW in an embedded variable, SQL variable, or SQL parameter have a length of 3 bytes, and must be in X'hhmmss' format.
    When time data is received in a predefined character string representation by using a by-row (ROW specification) interface, during the definition of a column, define the column as CHAR(8) rather than a time data type. Any time arithmetic operations should be specified using the TIME scalar function after the time involved has been converted into the time data type.
  4. For performing a retrieval or updating by row (ROW specification), the time stamp data type portion of the embedded variable, SQL variable, or SQL parameter with respect to ROW is (7 + p/2) bytes long, and it should be in the X'YYYYMMDDhhmmss[nn...n]' format. When time stamp data is received in a predefined character string representation using a by-row (ROW specification), when defining a column, define it as CHAR with a length of 19, 22, 24, or 26 bytes rather than as a time stamp data type column.
  5. If SYSTEM GENERATED is specified for the column in the table into which data is to be inserted, HiRDB ignores any data in the corresponding portion, and inserts the current date (CURRENT_DATE) for the DATE type, and the current time (CURRENT_TIME) for the TIME type.

Common rules

  1. For common rules on specifying the SELECT statement, see the common rules in INSERT statement Format 1 (Insert row).
  2. 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 Chpater 3.
  3. 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 referential constraints in specifying the SELECT statement, see the rules on referential constraints in INSERT statement Format 1.
  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.

Example

Insert into a stock table named STOCK one row of values that are read into the embedded variable XROW:

INSERT INTO STOCK(ROW)
   VALUES(:XROW)