Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Duplicate row error
 
From:   Reddy, Sreenivas

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023