Trying to find an elegant way to do this. file manipulation and scheduled tasks and may event gateways...

[John Ivanoff]

If I get a text file that has order numbers in it I need to query a DB
in the states to see if a record exists for that order number
If it does exist I skip the processing and then I'd like to mark that
the order already exists.
If it doesn't exist I need to query a DB in th UK to return a record
set for the order and a record set for parts, if there are any (Would
returning a structure be a good idea?)
Then I need to insert that data into the DB in the US. This seems to
take a looong time.
Then I want to make the order number that it importred.
If the insert fails roll back the transaction and mark the order
number that it failed.
Repeat until EOF.

Part of me wants to read one order number at a time since I'm not sure
what happens if I'm going through a list of 500 order numbers and it
times out. Or how can I prevent that since I don't know if I set the
timeout to 5 minutes that it might need 6.

I don't care it it takes 2 day for this to complete since it is
importing historic data.
That said that's why I would like to flag what is imported, what
failed or what already existed in the database to send a report so the
admin of the site know what happened the day before


I currently have a page that will read the first line of the text file
and do all stuff I want to do.
What I don't know how to do is how to mark that the line has been
processed. Either delete that row from that file then write it to a
"completed", "failed" or "exists" file and then attach those file in
an email at the end of the day.

Would it be better to load that file into a DB and then flag stuff in
there?


Since this might be done a few times a year what does having a
scheduled task fire off several times a day do to performance?

Is there an elegant way to do this? Not really looking code, maybe on
how to delete a line from the text file :-), but just some guidance.

Thanks,
John

[Derek Bumpas]

I think your best bet is probably to read the entire file into a
'work' table and process it there.

Iterative file operations are never a good practice if you can help it.

Then create an output file with whatever your changes are all at once.



On Aug 13, 2009, at 6:52 PM, John Ivanoff <john.i...@gmail.com>
wrote: