Inserts rows into a table in units of columns. The statement can be used to insert one row by directly specifying values. In addition, this command can also insert one or more rows by using a query expression body.

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.

INSERT INTO [[RD-node-name.]authorization-identifier.]
              table-identifier
     {[(column-name [, column-name]...)]
       {VALUES (insertion-value [, insertion-value]...)
      |query-expression-body}
      |DEFAULT VALUES}
     [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]

• [[RD-node-name.]authorization-identifier.]table-identifier

RD-node-name

Specify the RD node name of the RD node to be accessed.

authorization-identifier

Specifies the authorization identifier of the owner of the table.

MASTER cannot be specified as an authorization identifier.

table-identifier

Specifies the name of the table into which rows are to be inserted.

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

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

The following rules apply to column names:

1. Rows cannot be inserted, updated, or deleted from a read-only view table (for details about read-only view tables, see Common Rules in CREATE VIEW (Define view) in 3).
2. When row insertion into a view table is specified, the system inserts the rows into the base table of the view table. Any columns in the view table's base table that are not associated with a column in the view table receive the null value. Therefore, in the case of a view table defined from a base table with the FIX attribute, rows are not inserted if there are columns of the view table's base table that are not associated with columns in the view table.
3. Any column that is not specified receives the null value.
4. The number of elements in unspecified repetition columns will be 0.
5. Not specifying any column names is equivalent to specifying all columns in the specification order of the columns when the columns were defined in the table definition.
6. A subscript cannot be specified as part of a column name.

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

insertion-value

Specifies an insertion value for a specified column. The following items can be specified:

• Value expressions
• NULL (represents the null value)
• DEFAULT
• ARRAY [element-value[, element-value]...]^*

^* The following can be specified in element-value:

• Value expression
• NULL (represents the null value)
• DEFAULT

The following rules apply to insertion values:

1. Insertion values must be specified in the order in which the column names are specified.
2. To assign the null value as an insertion value, NULL must be specified.
3. If DEFAULT is specified in insertion-value or as a value of an element in insertion-value, the predefined value of the column that is the target of insertion is inserted. If the DEFAULT clause is present, the insertion value is the specified predefined value. If the DEFAULT clause is not present but WITH DEFAULT is specified, the insertion value is the predefined value for WITH DEFAULT. If neither the DEFAULT clause nor WITH DEFAULT is present, NULL is the predefined value.
4. If SYSTEM GENERATED is specified for the column associated with insertion-value, the specified insertion value is ignored; instead, the current date (CURRENT_DATE) is inserted for the DATE type, or the current time (CURRENT_TIME) for the TIME type.
5. In the case of preprocessing using the PREPARE statement, an embedded variable or an indicator variable cannot be specified.
6. Embedded variables must be of the same structures as the column structures of the associated columns.
7. In the case of an embedded variable used to assign a value to a ? parameter, the same structure as the structure of the associated column must be specified.
8. ARRAY [element-value [, element-value] ...] can be specified only if the corresponding column is a repetition column. The maximum number of elements that can be specified is 30,000, provided that they do not exceed the maximum number of elements allowed in the column into which they are inserted. The embedded variable (indicator variable) and the ? parameter for element values should be of a simple structure.
9. A value expression containing a column name or a set function cannot be specified in an insertion value.
10. Neither a WRITE specification nor a GET_JAVA_STORED_ROUTINE_SOURCE specification can be specified in an insertion value.

• query-expression-body:: = {query-specification
  | (query-expression-body)
  | query-expression-body {UNION | EXCEPT} [ALL]
  {query-specification | (query-expression-body) } }
  

Specifies the query specification body that fetches the data to be inserted.

For details about query expression bodies, see 2.2 Query expressions.

• query-specification:: = SELECT [{ ALL | DISTINCT}]
  {selection-expression [, selection-expression]... | *} table-expression
  

For details about query specifications, see 2.3 Query specification.

If the column into which the fetched data is to be inserted is a repetition column, specify an unsubscripted repetition column in the selection expression in the query specification associated with that column.

• table-expression:: = FROM table-reference [, table-reference]...
  [WHERE search-condition]
  [GROUP BY value-expression [, value-expression]...]
  [HAVING search-condition]
  

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

• DEFAULT VALUES

Inserts a predefined value for all columns in the row to be inserted.

DEFAULT VALUES has the same meaning as the specification of the following format:

VALUES(DEFAULT,DEFAULT,...)

The number of DEFAULT specifications above is equal to the number of columns in the table that is subject to the insertion operation.

• [WITH ROLLBACK]

Specifies that if the table that is subject to the insertion operation is being used by another user, the transaction issued by that user is to be cancelled and invalidated.

When the WITH ROLLBACK option is omitted and the table subject to the insertion operation is being used by another user, the current user must wait until the transaction issued by that user is completed.

1. Care must be taken that the embedded variable, SQL variable, or SQL parameter has either the same data type as the data type of the corresponding column or a data type that can be converted into the data type of the corresponding column.
2. The ? parameter can be specified in the INSERT statement only if the INSERT statement is preprocessed by the PREPARE statement. The value to be assigned to a ? parameter is specified in an embedded variable in the USING clause of the EXECUTE statement that is associated with the PREPARE statement that prepares the INSERT statement.
3. Embedded variables and indicator variables cannot be used in the INSERT statement or in an SQL procedure that is preprocessed by the PREPARE statement. For details about specification values in a Java procedure, see JDBC drivers or SQLJ in the HiRDB Version 8 UAP Development Guide. For details on search conditions, see 2.7 Search conditions.
4. The number of columns in a row to be inserted must equal the number of columns for which column names are specified.
   The values of these columns must be of either the data types of the columns or data types that can be converted into those data types. (Note that if the column into which the data is to be inserted is of the national character data type and a character string literal is specified as the insertion value, the character string literal will be treated as a national character string literal. When a character string literal is treated as a national character string literal, only the character data length is checked; the character codes are not checked.) When rows are to be inserted, the number of columns retrieved by a query specification body must be equal to the number of columns specified in column-name. The data types of the corresponding columns must be convertible data types. (If the column into which data is to be inserted is the national character data type and if a character string literal is specified in the selection expression for the query expression body, the character string literal is treated as a national character string literal. When a character string literal is treated as a national character string literal, only the character data length is checked; the character codes are not checked.)
   
5. When fixed-point or floating-point data is inserted into any of the following data type columns, any digits following the decimal point are rounded off:
   
   • INTEGER
   • SMALLINT
   During the insertion of fixed-point data into a column of the DECIMAL type, any digits below the scaling for the column are rounded off.
   
6. Character data greater than the length specified when the table was defined cannot be inserted.
7. Numeric data outside the range of the data types defined for a column cannot be inserted.
8. If the data to be inserted into a fixed-length character string column (including a national character string or a mixed character string column) is shorter than the length of the column, the data is left-justified in the column and trailing blanks are added.
9. The following items can be specified when data is inserted into a column of BLOB data type: embedded variables, ? parameters, SQL variables, SQL parameters, SUBSTR scalar function, function calls, and NULL.
10. When data is inserted into a column of an abstract data type, embedded variables and ? parameters cannot be specified in the values to be inserted.
11. A component specification cannot be specified in a selection expression in the query expression body.
12. When data is inserted into a column of an abstract data type, the following item cannot be specified in the values to be inserted: values of an abstract data type, including the BLOB attribute for which no LOB attribute storage RDAREA name was specified in the table definition.
13. Neither a WRITE specification nor a GET_JAVA_STORED_ROUTINE_SOURCE specification can be specified in a selection expression in the query expression body.
14. 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.
15. 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.

1. For the rules on inserting rows into a referenced table or referencing table, see the explanation of referencing actions in CREATE TABLE (Define table) in iChapter 3.

1. Insert rows with the values read into embedded variables into all columns of a stock table named STOCK:
   

   INSERT INTO STOCK
       VALUES(:XPCODE,:XPNAME,:XCOLOR,
              :XPRICE,:XSQTY)

2. Insert rows with the values read into embedded variables into the product code (PCODE), product name (PNAME), and quantity-in-stock (SQTY) columns of a stock table named STOCK:
   

   INSERT INTO STOCK
       (PCODE,PNAME,SQTY)
       VALUES(:XPCODE,:XPNAME,:XSQTY)

3. Insert into stock table STOCK all table data from stock table 2 (STOCK2) with the same column definition information as the STOCK stock table:
   

   INSERT INTO STOCK
     SELECT * FROM STOCK2

4. Insert data representing 612S PANTS, and WHITE into the following columns in a stock table (STOCK): product code (PCODE), product name (PNAME), and color (COLOR):
   

   INSERT INTO STOCK (PCODE,PNAME,COLOR)
     VALUES('612S',N'PANTS',N'WHITE')

5. Insert the following items into columns in an orders table (ORDERS): 02561, TT001, 302S, 50, current date (CURRENT_DATE), and current time (CURRENT_TIME):
   

   INSERT INTO ORDERS
     VALUES('02561','TT001','302S',50,
            CURRENT_DATE,CURRENT TIME)