Executing a select statement in batches
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect!
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