17.05 - Multistatement and Iterated INSERT Requests - 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)

Vantage 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. the 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