Hi,

I have a insert statement with values taken using a series of selects from 10 different tables.

Insert into TABLE 1 (col1, col2 col3, col4, col5, col6, col7)
Values (
select * from view 1
UNION
select * from view 2
UNION
select * from view 3
UNION
select * from view 4
UNION
select * from view 5
UNION
select * from view 6
UNION
select * from view 7
UNION
select * from view 8
UNION
select * from view 9
UNION
select * from view 10
)

This TABLE 1 has unique key (composite) contraint on col1, col2, col3, col4 & col5. All views from 1 to 10 have same columns including data types.

In my understanding UNION should eliminate duplicate rows. However this is giving me unique key violation on TABLE 1.

I verified data to find any duplicates for col1 to col5 (using count * and group by), but the result has no duplicates.

This is working fine in Oracle 9i, but failing in Oracle 10g.

Any thoughts?

Vinay