Multistatement and Iterated INSERT Requests - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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