November 5, 2012 at 9:38 am
Hello
I need to delete 1000 rows from table and it should do it batch wise.
means
i need to loop delete statement that delete top 1000 rows.
please help me
November 5, 2012 at 9:45 am
yogi123 (11/5/2012)
HelloI need to delete 1000 rows from table and it should do it batch wise.
means
i need to loop delete statement that delete top 1000 rows.
please help me
Why do you think you need to loop to delete the top 1,000 rows?
You didn't provide any details so it is impossible to provide much help.
If you need to use batches to delete rows from a table you can use something like this.
while @@ROWCOUNT > 0
delete top(1000)
from YourTable
Where ???
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 7, 2012 at 10:16 am
Sean Lange (11/5/2012)
yogi123 (11/5/2012)
HelloI need to delete 1000 rows from table and it should do it batch wise.
means
i need to loop delete statement that delete top 1000 rows.
please help me
Why do you think you need to loop to delete the top 1,000 rows?
You didn't provide any details so it is impossible to provide much help.
If you need to use batches to delete rows from a table you can use something like this.
while @@ROWCOUNT > 0
delete top(1000)
from YourTable
Where ???
Also - since there's no physical order in a DB, there had better be a good predictable ORDER BY clause added in to define how the TOP 1000 is supposed to find said 1000.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 7, 2012 at 10:27 am
Matt Miller (#4) (11/7/2012)
Sean Lange (11/5/2012)
yogi123 (11/5/2012)
HelloI need to delete 1000 rows from table and it should do it batch wise.
means
i need to loop delete statement that delete top 1000 rows.
please help me
Why do you think you need to loop to delete the top 1,000 rows?
You didn't provide any details so it is impossible to provide much help.
If you need to use batches to delete rows from a table you can use something like this.
while @@ROWCOUNT > 0
delete top(1000)
from YourTable
Where ???
Also - since there's no physical order in a DB, there had better be a good predictable ORDER BY clause added in to define how the TOP 1000 is supposed to find said 1000.
It actually really doesn't make any difference here. The intention is to delete rows in a batch. Why not let sql decide which rows are the top? It will determine the top based on whatever it finds as the easiest way to get them. It doesn't make any difference the order they are deleted as long as at the end they are all deleted in the end.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 7, 2012 at 10:56 am
Sean Lange (11/7/2012)
Matt Miller (#4) (11/7/2012)
Sean Lange (11/5/2012)
yogi123 (11/5/2012)
HelloI need to delete 1000 rows from table and it should do it batch wise.
means
i need to loop delete statement that delete top 1000 rows.
please help me
Why do you think you need to loop to delete the top 1,000 rows?
You didn't provide any details so it is impossible to provide much help.
If you need to use batches to delete rows from a table you can use something like this.
while @@ROWCOUNT > 0
delete top(1000)
from YourTable
Where ???
Also - since there's no physical order in a DB, there had better be a good predictable ORDER BY clause added in to define how the TOP 1000 is supposed to find said 1000.
It actually really doesn't make any difference here. The intention is to delete rows in a batch. Why not let sql decide which rows are the top? It will determine the top based on whatever it finds as the easiest way to get them. It doesn't make any difference the order they are deleted as long as at the end they are all deleted in the end.
Well if you can align the ORDER BY to the CI, the delete won't have to revisit the same pages multiple times which might help improve writes. That said - if the intent is to keep cycling to run this across the entire table (instead of just doing the top 1000 once and then stopping), it likely won't make much of a difference.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 7, 2012 at 12:35 pm
Matt Miller (#4) (11/7/2012)
Sean Lange (11/7/2012)
Matt Miller (#4) (11/7/2012)
Sean Lange (11/5/2012)
yogi123 (11/5/2012)
HelloI need to delete 1000 rows from table and it should do it batch wise.
means
i need to loop delete statement that delete top 1000 rows.
please help me
Why do you think you need to loop to delete the top 1,000 rows?
You didn't provide any details so it is impossible to provide much help.
If you need to use batches to delete rows from a table you can use something like this.
while @@ROWCOUNT > 0
delete top(1000)
from YourTable
Where ???
Also - since there's no physical order in a DB, there had better be a good predictable ORDER BY clause added in to define how the TOP 1000 is supposed to find said 1000.
It actually really doesn't make any difference here. The intention is to delete rows in a batch. Why not let sql decide which rows are the top? It will determine the top based on whatever it finds as the easiest way to get them. It doesn't make any difference the order they are deleted as long as at the end they are all deleted in the end.
Well if you can align the ORDER BY to the CI, the delete won't have to revisit the same pages multiple times which might help improve writes. That said - if the intent is to keep cycling to run this across the entire table (instead of just doing the top 1000 once and then stopping), it likely won't make much of a difference.
Good point about the CI, didn't think about that. I would hazard a guess that with no order by on something like this it would just delete the rows in page sequence. Certainly if we just wanted to delete the top x number of rows we need an order by of some sort.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply