What will happen if users try to insert duplicate records in SET table.

Teradata: Inserting Duplicates in Set Tables

Have you wondered ever? What will happen if users are trying to insert duplicate records in a SET table. Lets try to check the same with a simple example.

Sample Table: Creating a SET table

CREATE SET VOLATILE TABLE tutorial_test_set
 (
   id INTEGER 
 )
PRIMARY INDEX(id)
ON COMMIT PRESERVE ROWS;

1st Record Population: Inserting 1st of records in the 'tutorial_test_set' table and insert will work fine.

INSERT INTO tutorial_test_set VALUES(1);
 
Output:
*** Insert completed. One row added. 

Duplicate Record Population: Inserting 2nd of records with same value in the 'tutorial_test_set' table and this time insert will fail with error.

INSERT INTO tutorial_test_set VALUES(1);

Output:
*** Failure 2802 Duplicate row error in tutorial_test_set.