I have an identical staging table and a final table for a dataset, with the
final table having a unique index on on four of the 16 fields. Recently I
have been getting duplicate errors in the script that performs the insert
query to load the final table. I'd like to perform a query that would
result all the rows, all fields from the records that violates the index
from the staging table.
My initial take on this was to perform a self join on the table and match on
the criteria
Where (T1.field1 = T2.field1
AND T1.field2 = T2.field2
AND T1.field3 = T2.field3
AND T1.field4 = T2.field4)
AND (NOT T1.field5 = T2.field5
OR NOT T1.field6 = T2.field6
...
OR NOT T1.field16 = T2.field16)
seems like an awful lot of typing to perform a query that is simply trying
to answer the question "Show me what is violating the index". Not only
that, but if the records are truly identical across all fields, I'd never
get a result.
