Multistatement and Iterated INSERT Requests - Teradata Database

Multistatement and Iterated INSERT Requests

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;

Note: 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