asked
Insert query
I am unable to exclude duplicate composite unique values and primary key values from a table and insert into another similar table. Table2 records to be inserted into Table1 while excluding duplicate primary key and composite unique key values Tabl1 has one primary key on col1 and one composite unique key on two (col2,col3)columns My queries are as follows. Qery1: Insert into table1 Select col1,col2,col3,col4,col5 From (select col1,col2,col3,col4,col5 from table2 where col2+col3 not in ( select col2+col3 from table1))as tb2 where col1 not in(Select col1 from table1) Above sub query is not returning any records even though exist. Query2: Select col1,col2,col3,col4,col5 from Table2 Except Select col1,col2,col3,col4,col5 From (select col1,col2,col3,col4,col5 from table2 where col2+col3 in ( select col2+col3 from table1))as tb2 where col1 in(Select col1 from table1) and col2 is not null and col3 is not null Second query above is returning records but insertion is throwing duplicate unique key violation. Please help me.
Is col2 and/or col3 nullable and contains at least one null value? If so, not in won't work as the entire subquery evaluates to null (e.g.unknown) and the outer query returns nothing. Try changing this to use not exists instead: select col1, col2, col3, col4, col5 from Table2 t2 where not exists ( select 'pk exists' from Table1 where col1 = t2.col1 ) and not exists ( select 'comp key exists' from Table1 -- assuming that 0 is not a valid value for these columns where isnull(col2,0) = isnull(t2.col2,0) and isnull(col3,0) = isnull(t2.col3,0) ) This should run faster too.
15 People are following this question.
