Delete 30,000

Question

Post reply

Delete 30,000

tt-615680

SSCertifiable

Points: 7835
More actions
November 11, 2009 at 3:19 am

#98268

Hi all,
I need to create a stored procedure that will delete 10,000 rows regularly; can anyone let me know how I would be able to do this please?
Thank you in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Ummm, you have given no details so this is a complete guess

Create procedure DeleteProc

as

Delete top 10000

from <yourtable>

go

And create a job to execute it in sqlagent

Clear Sky SQL
My Blog[/url]

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

Dave Ballantyne (11/11/2009)
Ummm, you have given no details so this is a complete guess
Create procedure DeleteProc
as
Delete top 10000
from <yourtable>
go
And create a job to execute it in sqlagent

Just a small syntax correction – The number 10000 should be in parentheses

Create procedure DeleteProc

as

Delete top (10000)

from <Yourtable>

go

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

Thats a habit im trying to pick up , but obviously failing 🙂

Clear Sky SQL
My Blog[/url]

tt-615680 SSCertifiable Points: 7835 More actions · Answer 4

Thank you for the reply, sorry I was not clear on my post, I have over 300000 rows in a table and I need to delete most of the rows but I need to leave some rows for testing purpose. Is there a way to create a loop to regularly to delete the 30000 rows or 10000 rows at a time?

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 5

You can have a delete statement that is using top in it as Dave Ballantyne showed you. Then you can run it in a loop. Here is one way of doing it in a loop that runs 10 times:

Delete top (30000) from <Yourtable>

Go 10

If you need something else, please explain what you need.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/