INSERT
Valid in: SQL, ESQL, DBProc, OpenAPI, ODBC, JDBC, .NET
The INSERT statement inserts rows into a table.
The INSERT statement has the following format:
[EXEC SQL [REPEATED]]INSERT INTO table_name [(column {, column})]
[VALUES (value{, value})] | [subselect]
The INSERT statement inserts new rows into the specified table. Use either the values list or specify a subselect. When using the values list, only a single row can be inserted with each execution of the statement. If specifying a subselect, the statement inserts all the rows that result from the evaluation of the subselect. The subselect must not select rows from the table into which you are inserting rows; specifically, you cannot specify the same table in the INTO clause of the INSERT statement and the FROM clause of the subselect.
The column list identifies the columns of the specified table into which the values are placed. When the column list is included, OpenSQL places the first value in the values list or subselect into the first column named, the second value into the second column named, and so on. The data types of the values must be compatible with the data types of the columns in which they are placed.
The list of column names can be omitted only if:
• You specify a subselect that retrieves a value for each column in table_name. 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 table_name.
• There is a one‑to‑one correspondence between the values 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.
Values in the values list must be string or numeric literals or one of the OpenSQL constants. When the column list is included, any columns in the table that are not specified in the column list are assigned their default value. A value must be specified for mandatory columns. (Mandatory columns are columns defined as not default or not null with no default specified.)
Last modified date: 08/14/2024