Multistatement and Iterated INSERT Requests - Teradata Database - Teradata Vantage NewSQL Engine

SQL Data Manipulation Language

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-03
dita:mapPath
fbo1512081269404.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

Teradata Database provides statement independence for multistatement INSERT requests and iterated INSERT requests. Statement independence enables multistatement or iterated INSERT requests to roll back only the statements that fail within a transaction or multistatement request and not all of the individual statements within the transaction.

These forms of multistatement requests support statement independence:
  • INSERT; INSERT; INSERT;
  • BT; INSERT; INSERT; ET;
  • BT; INSERT; INSERT;
  • INSERT; INSERT; ET;
  • INSERT; COMMIT;
  • INSERT; INSERT; COMMIT;
The client software you are using must support statement independence to prevent all multistatement requests and iterated INSERT requests in a transaction or multistatement request from being rolled back. Refer to the documentation for your client software for information on support for statement independence.

Most statement data errors resulting from multistatement INSERT requests only roll back failed requests within the transaction or multistatement request, but do not abort all of the statements within the transaction or request unless every INSERT request within the multistatement request aborts because of data-related errors. Teradata Database does not roll back the transaction itself if this happens, only the INSERT requests it contains.

Statement independence supports the following multistatement INSERT data error types:
  • Column-level CHECK constraint violations
  • Data translation errors
  • Duplicate row errors for SET tables
  • Primary index uniqueness violations
  • Referential integrity violations
  • Secondary index uniqueness violations
Statement independence is not enabled for multistatement INSERT requests into tables defined with the following options:
  • Triggers
  • Hash indexes
  • Join indexes
Statement independence is also not supported for the following SQL features:
  • INSERT … SELECT requests
  • SQL stored procedures