design question

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,
JenniferJennifer wrote:

Quote:

Originally Posted by

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

You could use DTS or Integration Services or some other ETL tool to
manipulate the data as it is loaded. Or you could load the data to a
working table and then populate your actual table using an INSERT
statement. Those are the two most common options for complex data
loads.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||"Jennifer" <J.Evans.1970@.gmail.comwrote in message
news:1167769570.931558.212480@.n51g2000cwc.googlegr oups.com...

Quote:

Originally Posted by

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.