The Multifunctioning DBA

Oct 30 2010   12:00PM GMT

Delete in Batch



Posted by: Colin Smith
Tags:
T-SQL

Today I needed to delete 28 Million Rows from a table and I did not want to do it as one large statement because that would kill my log.

Here is what I did.

–Set your rows to deal with
DECLARE @intloop int

Select @intloop = count(*) from Table
where Column < Something
While @intloop > 0
BEGIN

– how many to do in a batch
SET rowcount 500000

–do the delete
delete from TableĀ  where Something < Something

set rowcount 0
–decrement intLoop
SET @intLoop = @intLoop – 500000

print ‘Batch Complete’
print @intLoop
END

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: