CODEWITHSUNDEEP
sqlteradata
user2711819
user2711819

Reputation: 960

Insert into Teradata

insert into tablex (a,b,c)
select distinct a,b,c
from tableA;

when I run select distinct statement alone it shows 6 rows. When I run with insert it shows 0 rows inserted .

Is it a bug or AM I missing some thing.

#Teradata 13.10

masked original Query

INSERT INTO tablex
  (SYSTEM_ID,START_DATE,END_DATE,CURRENT_FLAG )                
SELECT DISTINCT                     
    ,s.SYSTEM_ID                     
    ,s.trans_DATE
    ,DATE '9999-12-31' 
    ,'X' 
FROM  s JOIN  cc
ON s.var_id=cc.var_id
WHERE s.sno = cc.sno
AND s.sno<>s.orino AND s.orino IS NOT NULL AND s.orino <> ''
AND cc.end_date=s.trans_date-1;

Upvotes: 2

Views: 10147

Answers (1)

dnoeth
dnoeth

Reputation: 60462

It's not a bug :-)

All six rows existed already in the target table and it's a SET table which automatically removes duplicate rows during an Insert/Select.

Upvotes: 4

Related Questions