16.10 - Iterated Requests - Teradata Database

Teradata Database SQL Fundamentals

Teradata Database
Release Number
June 2017
English (United States)
Last Update


Iterated requests do not directly impact the syntax of SQL statements. They provide an efficient way to execute the same single-statement DML operation on multiple data records, like the way that ODBC applications execute parameterized statements for arrays of parameter values, for example.

Several Teradata Database client tools and interfaces provide facilities to pack multiple data records in a single buffer with a single DML statement.

For example, suppose you use BTEQ to import rows of data into table ptable using the following INSERT statement and USING modifier:

   USING (pid INTEGER, pname CHAR(12))
   INSERT INTO ptable VALUES(:pid, :pname);

To repeat the request as many times as necessary to read up to 200 data records and pack a maximum of 100 data records with each request, precede the INSERT statement with the following BTEQ command:

   .REPEAT RECS 200 PACK 100
The PACK option is ignored if the database being used does not support iterated requests or if the request that follows the REPEAT command is not a DML statement supported by iterated requests.

The following tools and interfaces provide facilities that you can use to execute iterated requests.

Tool/Interface Facility
CLIv2 for workstation-attached systems using_data_count field in the DBCAREA data area
CLIv2 for mainframe systems using-data-count field in the DBCAREA data area
ODBC Parameter arrays
JDBC type 4 driver Batch operations
Microsoft OLE DB Provider for ODBC and Teradata ODBC Driver Parameter sets
  • .REPEAT command
  • .SET PACK command


The iterated request must consist of a single DML statement from the following list:
  • DELETE (excluding the positioned form of DELETE)
  • EXECUTE macro_name

    The fully-expanded macro must be equivalent to a single DML statement that is qualified to be in an iterated request.

  • UPDATE (including atomic UPSERT, but excluding the positioned form of UPDATE)

The DML statement may not be a CALL statement.

The DML statement must reference user-supplied input data, either as named fields in a USING modifier or as '?' parameter markers in a parameterized request.

Iterated requests do not support the USING modifier with the TOP n operator.

All the data records in a given request must use the same record layout. This restriction applies by necessity to requests where the record layout is given by a single USING modifier in the request text itself; but the restriction also applies to parameterized requests, where the request text has no USING modifier and does not fully specify the input record.

The server processes the iterated request as if it were a single multistatement request, with each iteration and its response associated with a corresponding statement number.

Statement Independence for Simple INSERTs

When an iterated request includes only simple INSERT statements, a failure of one or more INSERTs does not cause the entire request to be rolled back. In these cases, errors are reported for the INSERT statements that failed, so those statements can be resubmitted. INSERT statements that completed successfully are not rolled back.

This behavior is limited to requests submitted directly to the database using an SQL INSERT multistatement request or submitted using a JDBC application request.

Related Topics

For more information about:
  • The PACK option, see “Rules.”
  • Iterated request processing, see SQL Request and Transaction Processing, B035-1142.
  • Which DML statements can be specified in an iterated request, see SQL Data Manipulation Language , B035-1146 .
  • CLIv2, see Teradata Call-Level Interface Version 2 Reference for Workstation-Attached Systems.
  • ODBC parameter arrays, see ODBC Driver for Teradata User Guide.
  • JDBC driver batch operations, see Teradata JDBC Driver User Guide.
  • Microsoft OLE DB Provider for ODBC and the Teradata ODBC Driver, see ODBC Driver for Teradata User Guide.
  • BTEQ PACK command, see Basic Teradata Query Reference.