Jeff Moden (11/23/2007)
Yep... you're right... database sure can't do any of that And, I'm sure that the VB program does it with much more speed...
Everyone does realize that my statement above was absolutely dripping with sarcasm, correct?
I came very late to this party, but it seemed obvious to me.
I would be supicious about that bulletproof VB - bet it doesn't start off by reading everything from the table into store so that it can check that it's not inserting something that's already there. This seems to me like a complete giveaway:-
Ramoncito Medina (11/23/2007)hrThe thing is all of the extracted files that are inserted into the database have no duplicates, except for the one file that is generated last Nov 5, where our database server was reported stopped responding.
it seems pretty clear that duplicates of already present rows were not checked for when the job ran again after the outage (and that the job uses multiple transactions for the insert - probably using default ADO behaviour).
Also the question about the non-surrogate key was answered accidentally I think by the comment
There are no joins in my expressions, there is a unique index(ID).
which I take to mean that there is a surrogate key and no unique constraint except on that.
Tom