Re: Staging to final table (join question?)

"Travis" <trp9_at_home.com> wrote in message news:<gDRf7.36064$vW2.16432085_at_news1.sttln1.wa.home.com>...
> 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.
>
> If anyone has any guidance in this matter I'd really appreciate it.
>
> Thanks
> Travis

Travis
To select duplicates run the following...

select *
from T1
group by primary/unique key ( in your case the 16 columns) having count(*) > 1
/

To avoid these problems in the future I suggest that you create a unique index with these 16 columns on the production table. Received on Mon Aug 20 2001 - 11:55:55 CEST