Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Executing a select statement in batches

Posted on 2012-04-12
3
Medium Priority
?
202 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:Auerelio Vasquez
  • 2
3 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 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 40

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:Auerelio Vasquez
ID: 37840010
Perfect!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

876 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