4.2.1 Specification format and rules for the INSERT statement

The INSERT statement inserts rows into a table. You can insert a single row by specifying a value, or insert one or more rows by using a query expression body.

Organization of this subsection

(1) Specification format
(2) Explanation of specification format
(3) Privileges required at execution
(4) Rules
(5) Examples

(1) Specification format

■ To set insertion values on a column-by-column basis

INSERT-statement ::=
     INSERT INTO table-name [[AS] correlation-name]
                {[(column-name[, column-name]...)]
        {query-expression-body | VALUES(insertion-value[, insertion-value]...)}
                  | DEFAULT VALUES
                 }
 
insertion-value ::=  {value-expression | NULL | DEFAULT}

■ To insert by row

INSERT-statement ::=
     INSERT INTO table-name [[AS] correlation-name] (ROW)
         VALUES(row-insertion-value)
 
row-insertion-value ::=  dynamic-parameter

To Page Top

(2) Explanation of specification format

table-name

Specifies the name of the table into which rows are to be inserted (the insertion target table). For rules on specifying a table name, see (2) Table name specification format in 6.1.5 Qualifying a name.

You cannot specify the same table that is specified in the query expression body.

In addition, you cannot specify a read-only viewed table.

If you specify an updatable viewed table in table-name, note the following points:

When you insert rows into an updatable viewed table, the rows are inserted into the underlying table. At this time, rows can be inserted regardless of the search conditions specified when the viewed table was defined.
When rows are inserted into an updatable viewed table, default values are stored in the columns of the underlying table that do not correspond to the columns of the updatable viewed table. For details about the default values for columns, see 7.10 DEFAULT clause.

Note that if no default value for a column is specified in a DEFAULT clause, the null value is stored as the default value for the column.

Important: If the NOT NULL constraint (null values are not allowed) is defined on columns of the underlying table that do not correspond to the columns of the updatable viewed table, rows in which null values are stored in those columns cannot be inserted.

[AS] correlation-name

Specifies the correlation name of the insertion target table. For details about correlation names, see (4) Table specification format in 6.1.5 Qualifying a name.

(column-name[, column-name]...)

Specifies the names of the columns into which data is to be inserted.

Columns whose names are not specified will be filled with the default values specified in the DEFAULT clauses in the CREATE TABLE statement. However, in the following cases, the null value is stored as the default value for the column:

When no default value for the column has been specified in a DEFAULT clause in the CREATE TABLE statement

Note that if no column names are specified, it assumes that all the columns were specified, in the same order in which the columns were specified when the table was defined with the CREATE TABLE statement.

query-expression-body

Specifies a query expression body to be used to retrieve the data to be inserted. For details about the query expression body, see (b) query-expression-body in (2) Explanation of specification format in 7.1.1 Specification format and rules for query expressions.

The following rules apply:

The table specified in the query expression body cannot be the same as the table that is the target of the insertion.

VALUES (insertion-value[, insertion-value]...)

insertion-value ::= {value-expression | NULL | DEFAULT}

Specifies insertion values corresponding to the columns specified in the column-name specifications. Specify one of the following for insertion-value:

value-expression:

Specify the insertion value in the form of a value expression. For details about value expressions, see 7.20 Value expression.

Note the following rules:

value-expression cannot include a column specification.
insertion-value cannot include a table that is the same as the insertion target table.

NULL:

Specify this to set the insertion value to the NULL value.

DEFAULT:

Specify this to set the insertion value to the default value for the column specified in the DEFAULT clause of the CREATE TABLE statement. If no default value for the column was specified in a DEFAULT clause, the null value is assumed as the default value for the column.

DEFAULT VALUES

Specify this if you want to insert the default column values in all of the columns in the insertion target table.

Specifying DEFAULT VALUES is equivalent to specifying the following:

VALUES(DEFAULT,DEFAULT,...)

where the number of DEFAULT specifications is equal to the number of columns in the insertion target table.

If you specify DEFAULT VALUES for a table without a DEFAULT clause specification, the null value will be assumed as the default values for the columns, which means all of the columns will be assigned null values.

ROW

Specified to insert data by row. When you specify ROW, the entire row is inserted as a single item of data.

The rules for specifying ROW are as follows:

It can be specified only for a FIX table.
You cannot specify a query expression body.

VALUES(row-insertion-value)

row-insertion-value ::= dynamic-parameter

Specifies the data to be inserted into an entire row.

The assumed data type of the dynamic parameter is CHAR type. The data length is the row length of the table into which data is being inserted. Align the boundaries so that there are no gaps in the structure. For details about how to calculate the row length, see the ROWSZ calculation formula in Determining the number of pages for storing each type of row in the HADB Setup and Operation Guide.

Note that only one dynamic parameter can be specified.

To Page Top

(3) Privileges required at execution

To execute the INSERT statement, all of the following privileges are required:

The CONNECT privilege
INSERT privilege for a table to which rows are to be inserted
SELECT privilege for a table specified in a query expression body

Example

INSERT INTO "T1"
    ("C1","C2","C3")
    SELECT "C1","C2","C3" FROM "T2" WHERE "C3"<=100

The INSERT privilege for Table T1 and the SELECT privilege for Table T2 are required to execute the above INSERT statement.

To Page Top

(4) Rules

The total number of tables, derived tables, and table function derived tables specified in the INSERT statement cannot exceed 2,048. For rules on how to count the number of tables, derived tables, and table function derived tables specified in an SQL statement, see (4) Rules in 4.4.1 Specification format and rules for the SELECT statement.
If the set operations specified in the INSERT statement are all UNION, a maximum of 1,023 set operations can be specified. However, if the specified set operations include EXCEPT or INTERSECT, no more than 63 set operations can be specified.
A maximum of 63 outer joins (FULL OUTER JOIN) can be specified in the INSERT statement.
When insertion values are set on a column-by-column basis, the number of insertion values must be the same as the number of column names. Note also that the data types of the insertion values must be the same as the data types of the columns into which the data is being inserted, or else they must be converted into assignable data types. For details about converting data into assignable data types, see 6.2.2 Data types that can be converted, assigned, and compared.

Example:
```
INSERT INTO "T1" ("C1","C2","C3")
    VALUES('U00358',5,DATE'2011-09-08')
```
In this case, the following rules must be observed:
- Because three columns (C1, C2, and C3) are specified, three insertion values must also be specified.
- The data types of the insertion values must be the same as the data types of columns C1, C2, and C3, or they must be converted into assignable data types. For example, if column C3 is type DATE, its insertion value data must also be made type DATE.
If you specify a dynamic parameter as an insertion value, its assumed data type and data size will be the data type and data size of the column into which it is being inserted.
If you insert DECIMAL or DOUBLE PRECISION type data into a column with any of the data types listed below, the fractional (decimal) part will be truncated:
- INTEGER
- SMALLINT
Furthermore, if you insert DECIMAL type data into a DECIMAL type column, any digits beyond the scaling specified for the column will be truncated. If you insert DOUBLE PRECISION type data into a DECIMAL type column, any digits beyond the scaling specified for the column will be rounded off (to the nearest even number).
You cannot insert character string data or binary data that is longer than the row length specified when the table was defined.
You cannot insert numeric data outside the numeric range of the data type defined for a column.
If the data being inserted into a CHAR type column is shorter than the column size, the data is left-aligned in the column and trailing spaces are added.
If the data being inserted into a BINARY type column is shorter than the column size, the data is left-aligned in the column and the rest of the field is set to X'00'.
This statement cannot be used to insert rows into a dictionary table or system table.
The same operation or design that can be used when the INSERT statement is run for row store tables cannot be used when the statement is run for column store tables. For details, see Criteria for selecting row store tables and column store tables, Checking whether a single-chunk table needs to be reorganized, and Checking whether a multi-chunk table needs to be reorganized in the HADB Setup and Operation Guide.

To Page Top

(5) Examples

Example 1: Insert rows by specifying VALUES

Insert the following data (row) into the sales history table (SALESLIST):

Customer ID (USERID): U00358
Product code (PUR-CODE): P003
Quantity purchased (PUR-NUM): 5
Date of purchase (PUR-DATE): 2011-09-08

INSERT INTO "SALESLIST"
      ("USERID","PUR-CODE","PUR-NUM","PUR-DATE")
      VALUES('U00358','P003',5,DATE'2011-09-08')

Example 2: Insert rows by specifying VALUES (specifying subqueries in the insertion value)

Insert the following data (row) into the sales history table (SALESLIST):

Product code (PUR-CODE): P003
Product name (PUR-NAME): the product name corresponding to product code P003 in the product table (PRODUCTLIST)
Product color (PUR-COL): the product color corresponding to product code P003 in the product table (PRODUCTLIST)

INSERT INTO "SALESLIST"("PUR-CODE","PUR-NAME","PUR-COL")
    VALUES('P003',
          (SELECT "PUR-NAME" FROM "PRODUCTLIST" WHERE "PUR-CODE"='P003'),
          (SELECT "PUR-COL" FROM "PRODUCTLIST" WHERE "PUR-CODE"='P003'))

Example 3: Insert rows by specifying a query expression body

Insert data from the north district sales history table (SALESLIST_N) into the sales history table (SALESLIST).

Assume that the sales history table (SALESLIST) and the north district sales history table (SALESLIST_N) have the same column structure.
Insert data where the date of purchase (PUR-DATE_N) in the north district sales history table (SALESLIST_N) is on or after September 6, 2011.

INSERT INTO "SALESLIST"
      ("USERID","PUR-CODE","PUR-NUM","PUR-DATE")
      SELECT "USERID_N","PUR-CODE_N",
             "PUR-NUM_N","PUR-DATE_N"
          FROM "SALESLIST_N"
          WHERE "PUR-DATE_N">=DATE'2011-09-06'

Example 4: Insert rows using the ROW specification

Add new sales information to the sales history table (SALESLIST) (insert using the ROW specification). The columns that comprise the sales history table are customer ID (USERID), product code (PUR-CODE), quantity purchased (PUR-NUM), date of purchase (PUR-DATE).

INSERT INTO "SALESLIST"(ROW)
    VALUES(?)

To Page Top