Data warehousing environments require frequent updates of their underlying databases, and the overall data load strategy must include scheduled incremental loads, or small batches, to be performed throughout the day.
The intent of the LOGGING ERRORS option with INSERT ... SELECT and MERGE batches is to complement the FastLoad and MultiLoad batch load utilities that handle larger volumes of data and more complex loads with multiple target tables or DML operations, and the continuous load utility, Teradata Parallel Data Pump.
By capitalizing on incremental batch loads on a continuous basis using INSERT ... SELECT or MERGE requests or both, you can update your data warehouse as frequently as is necessary, using a mixture of data loading strategies. Furthermore, such batch requests are not restricted by the inability to load data into tables defined with LOB columns (however, error logging does not support LOB data. LOBs in a source table are not copied to an error table; instead, they are represented in the error table as nulls.), unique secondary indexes, referential integrity constraints, triggers, and join indexes that can make bulk data loading into active data warehouse tables a complicated operation (see Teradata® FastLoad Reference, B035-2411 and Teradata® MultiLoad Reference, B035-2409 for a complete list and description of the database data table restrictions they must observe).
Teradata Parallel Data Pump performs batch loads, but the INSERT ... SELECT and MERGE batch options and the Teradata Parallel Data Pump option are for different situations. For example, when sessions are near capacity, INSERT ... SELECT or MERGE batch options are preferable to Teradata Parallel Data Pump. Conversely, when the specific order of updates is important, Teradata Parallel Data Pump is preferable.
- Insert errors that occur during an INSERT ... SELECT operation
- Insert and update errors that occur during a MERGE operation
When an insert or update error occurs on an index or data conversion, the transaction or request containing the erring statement does not roll back, but instead runs to completion, logging the rows that caused errors in an error table.
Data errors that cause secondary index or referential integrity errors do cause the transaction or request containing the erring statement to roll back. You must then fix the errors that caused the secondary index or referential integrity violations before you can again load those rows into the database.
Error tables are one component of a batch system that includes extensions to MERGE and INSERT ... SELECT batch load operations to support complex error handling. These DML statement extensions permit you to specify error logging for INSERT ... SELECT and MERGE operations. If you specify error logging, the system logs error conditions and captures copies of the data rows that cause them in an error table.
Error Logging Limits
You can specify WITH LIMIT OF error_limit in an INSERT SELECT or MERGE request with error_limit in the range [1, 16000000]. This limit is based on the largest number a system semaphore counter can support.
If you do not specify WITH LIMIT OF error_limit, error_limit defaults to 10.
If you do not want an error limit, specify WITH NO LIMIT.
If the INSERT SELECT or MERGE request accumulates the number of errors in the specified error limit, the request or transaction aborts in ANSI or Teradata mode, respectively, and changes to the target table rows are rolled back.
The database does not roll back error table rows previously logged by the aborted request or transaction.
For details, see INSERT/INSERT ... SELECT and MERGE.