Hi,

I am trying to insert into a table by selecting certain columns from other table. The first table does contain some rows. The select produces some rows which are already there in first table and also produces some new rows. These new rows are what I want to insert into first table.

I am using an sql like

Insert into table1
select colA,colB,colC from table2

I get sql -803 as the table 1 is not empty and there is a unique index defined.

Is there a way to insert only the non duplicates from 'select colA,colB,colC from table2' ?

Thanks
Mahendra

hi.

you're defined fk in some column?

No but I do have a unique primary key in table1 and that prevents the dups.

Insert into table1 a
select colA,colB,colC from table2 b
where not exists (select a.colA from table1 where a.colA = b.colA)

This assumes that the primary key is colA.

I am not 100% sure this works on an "insert into select from" statement, or if it does work, which version is DB2 is required (maybe on 8.2 only or does not work at all on any version). But the subselect is valid syntax for pure select statement.

no primary key, foreing key in the destination table? or a column with not null?