March 18, 2010 at 8:49 am
I have a code:
BEGIN
SET ROWCOUNT 10000;
BEGIN TRANSACTION
delete FROM table1
COMMIT
END
What code should I add, as soon as commits 10000, delete again.
Thank you
March 18, 2010 at 9:21 am
This is how I do that kind of thing:
SELECT 1
WHILE @@rowcount > 0
DELETE TOP (10000) FROM table1;
The initial "select 1" sets the @@rowcount to 1, then it keeps deleting as long as there are rows to delete.
Of course, without a Where clause to limit which rows to get rid of, this will eventually delete all rows in the table. If that's really what's desired, use Truncate instead.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 18, 2010 at 9:27 am
I do have a where clause.
so I would do:
SELECT 1
WHILE @@rowcount > 0
DELETE TOP (10000) FROM table1 where date <=3/18/2009
right?
Thank you
March 18, 2010 at 9:36 am
That should work. You are looking as long as deletes occur. If you happen to delete the last 1000 rows and rowcout = 1000, you look again, even if there are no rows to delete. The next delete won't do anything if there are no more rows meeting your WHERE clause.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply