Executing a select statement in batches

Hi,

I would like to take this statement:

delete from table where date <= '12/31/2000'

This coudl result in over 10,000,000 rows or more.

I want to do this in batches of 20,000 in a script. Any suggestions on the best way to do this?

Thanks,

Basil
LVL 1
Auerelio VasquezETL DeveloperAsked:
Who is Participating?
 
lcohanConnect With a Mentor Database AnalystCommented:
You should look at SET ROWCOUNT = xxx and use that to build your batch delete
http://msdn.microsoft.com/en-us/library/ms188774(SQL.105).aspx

smething like:

SET ROWCOUNT 2000
      DECLARE @rc int
      SET @rc=2000
      WHILE @rc=2000
BEGIN
      delete from orders with (rowlock) where chargestatus = 8
    SELECT @rc=@@rowcount
END
SET ROWCOUNT 0
0
 
lcohanDatabase AnalystCommented:
Or to be more speciffic:


SET ROWCOUNT 20000
      DECLARE @rc int
      SET @rc=20000
      WHILE @rc=20000
BEGIN
     delete from table where date <= '12/31/2000'
     SELECT @rc=@@rowcount
END
SET ROWCOUNT 0

and m,ake sure you have a index on table.date column and you could include a

UPDATE STATISTICS table in above batch delete after each 1 000 000 rows for instance
0
 
Auerelio VasquezETL DeveloperAuthor Commented:
Perfect!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.