>Oleg,
>
>Instead of deleting once every 2-3 weeks, you might consider defining a job on the server to delete your 'unused' rows everynight based on the appropriate conditions
>
>Mark
>
>
>
>>>>Hi all,
>>>>Every 2-3 weeks I load 0.5-1.5 million rec into server table and delete "expired" records. I delete records from server table using BCP+Fox solution (DROP INDEX for all of them, BCP the whole table (3-5 mln rec) out, APPEND FROM tblOld.txt FOR myConditions to Fox table, COPY TO tblNew.txt, TRUNCATE server table, BCP in, CREATE INDEX ).
>>>>It works good enough and using BCP+Fox I avoid transaction log nightmare that kills all SQL Server solutions I tried, but I am still interested in "Server only" solution and wondering how others deal with such problem.
>>>>TIA and Happy New Year!
>>>>Oleg
>>>
>>>Are the records being deleted coming only from the records you load every 2 to 3 weeks? If so, [at least in Oracle], you can use a BEFORE INSERT TRIGGER to prevent non-qualifying records from being inserted. Any other records already in the table would have to be deleted using the DELETE FROM command.
>>
>>No, I delete records that were inserted 3 months ago and were not used.
>>Thanks
>>Oleg
Mark,
These records expire 3 month after their "mail" day and it means all 1 million rec (minus 10,000 that were used) expire at once.
So, for 2 weeks this "delete job" will do nothing, then it will try to delete 1 mln rec.
I know severeal workarounds (including my BCP-Fox solution), it is beyond the scope of this thread.
Let us just assume you must delete 1 mil rec from 4 mil and you never heard about Foxpro (and BCP?).
The question is how you delete 1 mil rec?
Anybody has this experience?
Thanks
Oleg