how to iterate 21 deletes (all same table with different conditions) with small batches each time.

SQL 2008:
21 such deletes all for same table, same 2 columns in where condition, but the values of CONDITION1 & CONDITION2 vary each time.

but there are millions of records in these 21 deletes. it will overwhelm the log..

how can we automate that it will take 5k or 10k records at a time and keep iterating till all the 9 million records are removed?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
Can you pull off a 'DELETE TOP 5000 ...', followed with a SELECT @@ROWCOUNT, then throw it in a WHILE loop that bails when @@ROWCOUNT = 0 ?
Ryan McCauleyEnterprise Analytics ManagerCommented:
That's for sure what I'd recommend too - I always do incredibly large batch DELETE operations in the following format:

DELETE TOP (1000) -- or whatever number you want here
FROM SomeTable
WHERE col1 = @first and col2 = @second

Open in new window

If you've got multiple pairs of filter values to delete, you can either join in your delete statement to a table that contains those values, or possibly cursor over them to delete the rows one set of filter values at a time.

I assume that while you're deleting millions of rows, you're leaving a large portion of the table intact (as opposed to deleting most of the table's contents and leaving only a small portion intact). If you're actually deleting the vast majority of the records, you might want to consider a "Partition Switch", which is a handy trick where you swap one partition in a table out for a partition in another table (with the exact same DDL and indexes):

It's normally used for bulk loading or ETL, but it's handy when you're doing deletes too - you create a table containing rows you want to keep, and then swap out partition 1 from each table (which effectively switches the contents of the entire table if it's unpartitioned), leaving your table containing just the records you selected out. It happens almost instantly, takes no additional log space, and you can then either TRUNCATE or DROP your standby table.

Good luck!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
25112Author Commented:
thanks, that helped... and appreciate that extra pointers, ryan.

good day-
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.