CREATE TABLE tab1
( col1 INT
, col2 CHAR(1)
)
INSERT INTO tab1 VALUES (1, 'A')
INSERT INTO tab1 VALUES (1, 'B')
INSERT INTO tab1 VALUES (1, 'C')
INSERT INTO tab1 VALUES (1, 'D')
INSERT INTO tab1 VALUES (1, 'E')
INSERT INTO tab1 VALUES (8, 'F')
INSERT INTO tab1 VALUES (9, 'G')
----------------------------
BEGIN
DECLARE @N INT -- Number of rows to delete per batch
DECLARE @cnt decimal(18,2) -- Total count of rows matching specified criterion
DECLARE @loops INT -- Number of times the DELETE statement should loop to delete all relevent records
SET @N = 2
SELECT @cnt = COUNT(*) FROM tab1 WHERE col1 = 1
SET @loops = CEILING(@cnt/@N)
WHILE @loops > 0
BEGIN
DELETE TOP (@N) FROM tab1 WHERE col1 = 1
SET @loops = @loops - 1
END
END
-----------------
SELECT * FROM tab1
DROP TABLE tab1
