Description
The INSERT statement inserts new rows into the specified table.
The list of column names can be omitted under the following circumstances:
• A subselect is specified that retrieves a value for each column in tablename. The values must be of an appropriate data type for each column and must be retrieved in an order corresponding to the order of the columns in tablename.
• There is a one-to-one correspondence between the expressions in the values list and the columns in the table. That is, the values list must have a value of the appropriate data type for each column and the values must be listed in an order corresponding to the order of the columns in the table.
A value cannot be inserted into a SYSTEM_MAINTAINED TABLE_KEY or OBJECT_KEY column (because the values for these data types are system-generated). For this reason, the column names must be specified when inserting into a table that has logical key columns.
When including the column list, any columns in the table that are not specified in the column list are assigned a default value or a null, depending on how the column was defined when the table was created. For details about column defaults, see
CREATE TABLE.
Expressions in the values list can only be constants (including the null constant), scalar functions on constants, or arithmetic operations on constants.
Note: To insert long varchar or long byte columns, specify a DATAHANDLER clause in place of the host language variable in the VALUES clause. For details about data handler routines, see the chapter “Working with Embedded SQL” and the Embedded SQL Companion Guide. The syntax for the datahandler clause is as follows:
datahandler(handler_routine ([handler_arg]))[:indicator_var]
Note: If II_DECIMAL is set to comma, you must follow any comma required in SQL syntax (such as a list of table columns or SQL functions with several parameters) by a space. For example:
SELECT col1, IFNULL(col2, 0), LEFT(col4, 22) FROM version;
An INSERT/SELECT into a heap table that satisfies the bulk load criteria (not journaled and no secondary indexes) is executed with one bulk-load operation instead of multiple row-by-row inserts. This style of INSERT is faster and does much less transaction logging than row-by-row inserting. The inserted rows start on a new page, however, so many bulk-loaded INSERT/SELECTs of a few rows each may result in unexpected space usage in the result table.
Last modified date: 08/29/2024