General Rules for INSERT in Embedded SQL and Stored Procedures
The following rules apply to both forms of INSERT in embedded SQL and stored procedures.
The row to be inserted is constructed by building a candidate row as follows:
- Define each column value of the row to be NULL.
- Assign each inserted value to the corresponding column.
| Result Row Value for Any NOT NULL Column | Insertion Result |
|---|---|
| Non-null | Succeeds. |
| Null | Fails. SQLCODE is set to -1002. |
Insert values are set in the corresponding row column values according to the rules for defining host variables.
If the table identified by table_name is a view that was defined WITH CHECK OPTION, then the row to be inserted must be in the set of rows selected by the view.
Valued INSERT in Embedded SQL
When using the valued form of INSERT with embedded SQL, the colon is optional with host variables in the VALUES clause.
Rules for INSERT ... SELECT in Embedded SQL and Stored Procedures
The following rules apply to using the selected form of INSERT with embedded SQL and stored procedures:
- The number of rows in the temporary table returned by subquery determines the number of rows to be inserted.
- If an error occurs after one or more of the selected rows has been inserted, then the value -1002 is returned to SQLCODE and the current transaction is terminated with rollback.
- If subquery selects no rows, then the value +100 is returned to SQLCODE and no rows are inserted.