is there a keyword in sql that will allow me to run a delete statement that
delete's 1000 rows (example) but breaks the processing into deleting 100
rows 10 times?

TIA,
KevinE.

Not a single keyword, but you could do:

SET ROWCOUNT 100
DECLARE @i TINYINT
SET @i = 1
WHILE @i <= 10
BEGIN
    DELETE ...
    SET @i = @i + 1
END
SET ROWCOUNT 0



Show quoteHide quote

If you need to perform mass deletes 10,000+ records and the process is time
consuming due to transaction logging, then you may want to consider using
ALTER DATABASE.. to temporarily set your database recovery model to "simple
recovery" and then reset back to "full recovery" when finished. Also, place
a checkpoint after each delete statement.

Show quoteHide quote

SET ROWCOUNT 100

WHILE 1=1 BEGIN
  DELETE FROM T WHERE <condition>
  IF @@rowcount = 0 BREAK
END

SET ROWCOUNT 0

To limit the total number of rows deleted, you can use
a loop with a fixed number of iterations.

Steve Kass
Drew University

KevinE wrote:

Show quoteHide quote

is there a set-based solution to this task?
Select Query
Multilanguage support
SQL performance problem
Combining Two Tables ?