Solved

Executing a select statement in batches

Posted on 2012-04-12
3
198 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 40

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 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Detach & Attach 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.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

628 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