|
|
Posted by --CELKO-- on 11/02/07 16:49
>>.. the source file is immutable. I don't control it and it would take an act of God (not to mention an epoch and a king's ransom) to get it changed. <<
The fifth labor of Hercules was to clean the stables of King Augeas in
a single day. The Augean stables held thousands of animals and were
over a mile long. This story has a happy ending for three reasons: (1)
Hercules solved the problem in a clever way (2) Hercules got one tenth
of the cattle for his work (3) At the end of the story of the Labors
of Hercules, he got to kill the bastard that gave him this job.
>> I am bulk-loading the delete statements into a table and cursoring through them. <<
Write a routine that will pull off the keys from the statements and
load them into a working table. This SQL and not BASIC! The
statement is then something like this:
DELETE FROM Foobar
WHERE Foobar.keycol
IN (SELECT keycol FROM WorkingTable);
or use an EXISTS() instead of an IN().
>> These statements can be executed in any order and there are no foreign key constraints. <<
You can add a clustered index to the working table. Do you need to
check for orphaned rows in what should have been a referenced table?
That can be a bitch, depending on the depth of the referencing.
If the system continues to grow, you will benefit from hashing instead
of indexing to find matches.
>> The bulk load puts the individual delete statements in with a rowID column, so I think I might have my solution. <<
Row_id? You mean like a 1950's magnetic tape file sequential record
number? So you can do a sequential tape merge in SQL with a cursor?
You might want to move to an RDBMS mindset instead. Make the
identifier the primary key of the working table.
>> I know, fixing it at the source would by far be the better way, but at some point you just quit beating your head against immovable brick walls. ;^ <<
1) Read the Hercules story again. Plot revenge.
2) Hire an expensive outside consultant who will tell management the
same thing. They will suddenly grow ears!
Navigation:
[Reply to this message]
|