Duplicate Rows and INSERT … SELECT - Teradata Database

Duplicate Rows and INSERT … SELECT

If an INSERT using a SELECT subquery will create duplicate rows, the result depends on the table definition:

Duplicate rows are permitted in a MULTISET set only if no UNIQUE constraints or UNIQUE indexes are defined on the table.

Duplicate rows are not permitted in a SET table.

The following table summarizes the restrictions on duplicate rows and INSERT … SELECT:

FOR this table type …	Duplicate rows are …
MULTISET with no unique constraints	permitted. Inserted duplicate rows are stored in the table.
MULTISET with unique constraints	not permitted. An error message is returned to the requestor. The following constraints are considered to be unique in Teradata Database: Unique primary index Unique secondary index Primary key UNIQUE column constraint GENERATED ALWAYS identity column constraint. For nontemporal tables, unique secondary indexes, primary keys, and UNIQUE constraints are all implemented internally as unique secondary indexes. For information about how USIs, PKs, and UNIQUE constraints are implemented for temporal tables, see ANSI Temporal Table Support and Temporal Table Support.
SET	not permitted. In ANSI session mode, the system rejects the request and returns an error message to the requestor. If some other error, such as violating a uniqueness constraint, occurs first, then the system returns that error to the requestor rather than a duplicate row message In Teradata session mode, Teradata Database does the following. Rejects the duplicate rows in the transaction. Inserts the non‑duplicate rows into the table. Does not return an error message to the requestor.

In Teradata session mode, if an INSERT … SELECT request specifies a SAMPLE clause that selects a set of rows from a source MULTISET table, and then inserts them into a target SET table, and the sampled row set contains duplicates, the number of rows inserted into the target SET table might be fewer than the number requested in the SAMPLE clause.

In Teradata session mode, the condition occurs because SET tables reject attempts to insert duplicate rows into them. The result is that the INSERT portion of the INSERT … SELECT operation inserts only distinct rows into SET target tables. As a result, the number of rows inserted into the target table can be fewer than the number specified in the SAMPLE clause.

For example, if an INSERT … SELECT request SAMPLE clause requests a sample size of 10 rows, and there are duplicate rows from the MULTISET source table in the collected sample, the system rejects the duplicate instances when it attempts to insert the sampled rows into the SET table and inserts only the distinct rows from the sample set. That is, you could request a sample of 10 rows, but the actual number of rows inserted into the target table could be fewer than 10 if there are duplicate rows in the sampled row set. Teradata Database does not return any warning or information message when this condition occurs.