17.05 - INSERT in Embedded SQL and Stored Procedures - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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.