Solved

Executing a select statement in batches

Posted on 2012-04-12
3
195 Views
Last Modified: 2012-04-12
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
0
Comment
Question by:basile
  • 2
3 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 37839964
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
 
LVL 39

Expert Comment

by:lcohan
ID: 37839976
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
 
LVL 1

Author Closing Comment

by:basile
ID: 37840010
Perfect!
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question