>>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
>
>I like drop table for that volume.
>
>So I would copyt good to a temp table, drop the table, recreate it and bring the good data back. Recreate the indexes.
Stephen and Andrew,
SELECT "good data" into temp (3 mil rec) and back (or DROP+RENAME instead of bringing back) will have pretty much same problems as DELETE (it is "logged" operation).
Mark Allen suggested separate table for each mail drop and in this case SELECT INTO will copy only 10,000 rec. OTOH using several tables instead of 1 will require changes in applications.
I was hoping that there is some magic trick to turn off transaction log for mass DELETE or SELECT INTO.
Thanks,
Oleg