|
|
Posted by David Portas on 01/02/07 20:51
Jennifer wrote:
> 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/ms130214(en-US,SQL.90).aspx
--
[Back to original message]
|