Archives of the TeradataForum

Message Posted: Thu, 12 Oct 2006 @ 12:43:08 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

An INSERT/SELECT from a MULTISET table to a SET table INSERTs all unique rows, but silently drops all duplicate rows. No error code/message is generated.

INSERT/SELECT will create duplicate rows, then the result depends on the table definition.

Duplicate rows are permitted in a table defined as MULTISET if no UNIQUE constraints or UNIQUE indexes are defined.

Duplicate rows are never permitted in a table defined as SET.

FOR this table type Duplicate rows are ....

     ------------------------------------------------------------------------
     MULTISET with no permitted.
     unique constraints
     Inserted duplicate rows are stored in the table.
     ------------------------------------------------------------------------
     MULTISET with not permitted.
     unique constraints
     An error message is returned to the requestor.
     ------------------------------------------------------------------------
     SET not permitted.

     In this session mode The Teradata Database ...
     ------------------------------------------------------------------------
     ANSI rejects the entire transaction and returns
     an error message to the requestor.
     ------------------------------------------------------------------------

Teradata rejects the duplicate rows in the transaction, inserts the non-duplicate rows into the table, and does not return an error message to the requestor.

If the session mode is ANSI, the CREATE TABLE defaults to CREATE MULTISET TABLE, and if the session mode is TERADATA, CREATE TABLE defaults to CREATE SET TABLE.

In this scenario, the table Temp1 is created as SET table hence one can't enter the duplicate rows into table Temp1.

Thank You

Sreenivas Reddy.S.

	<Prev	Next>		<<First	<Prev	Next>	Last>>