Insert into Select * for a SET table


Insert into Select * for a SET table

Just curios to know why duplicates are silently discarded when do a INSERT INTO <<SET Table>> SELECT * FROM <<TBL>>, but throws a duplicate error when we do a manual Insert of dups - INSERT INTO <<SET table>> VALUES (**bleep**);


Why does it happen this way ?


  • Insert into Select
  • manual inserts
  • SET table

Re: Insert into Select * for a SET table


I guess it is because when you do INSERT SELECT * ... TD creates a spool and dumps the data into spool first. After which it merges the data from spool into Target table. Since the table is SET table, it silently discards duplicate without any error during merging.

In case of INSERT.. VALUES... , TD directly insert records into Target table. As it is defined as SET table, so row level constraint exists which on violation returns error.