Posted by Russ Rose on 01/03/07 01:16

"Jennifer" <J.Evans.1970@gmail.com> wrote in message
news:1167769570.931558.212480@n51g2000cwc.googlegroups.com...
> I've got a file I need to load into a table. The file contains
> modifiers to a fast food order. For example, if you ordered a burger
> and asked for no onions, the no onions request would be in the modifier
> file.
>
> So, the problem is there are "fake" duplicates in the file. What I
> mean is this:
> The file contains a Header ID, Detail ID and Modifier ID. Sometimes
> the Modifier ID is duplicated for a certain detail record. But...it's
> not a true duplicate. The record also contains what is modified. So,
> the "No Onions" record might have a modifier ID of 1. For the same
> detail line, there might be another Modifier record of "Add Cheese".
> But that will also have the Modifier ID of 1. It doesn't happen very
> often. And yes, it should be fixed in the program that creates the
> files. But that's not happening.
>
> The primary key on the table (I'm not allowed to change it) is the
> Header ID, Detail ID, Modifier ID, Store #, and Business Date.
>
> What I've done is moved the duplicates to a temporary file and inserted
> the rest of the records. Next, I'm assigning new Modifier IDs to these
> "duplicated" records, and then inserting them. This is working fine
> for me up to a point. The problem is when the file is loaded twice.
> These "duplicated" records are inserted again. And now they really are
> duplicates, even though they have different modifier IDs. And no, I
> don't expect the file to be loaded twice, but you just never know.
> This is bugging me and I'm not really sure of a way to get around it.
> I thought I'd throw it to the group here to see if anyone has run into
> this before. Any ideas are appreciated.
>
> Thanks,
> Jennifer
>

Three possibilities:
1: Pre-process the entire file before loading, re-numbering any duplicates
found in a predictable manner so a second load would generate the same key
values and all duplicates would be rejected.
2. Use the "Modifier" text as part of a virtual primary key during the
secondary load.
3. Learn to live with your duplicates and use select distinct to "roll up"
multiple 'Add Cheese' requests.

[Back to original message]