DELETE Top N records in a batch script (T-SQL)

BEGIN

-- Number of rows to delete per batch
DECLARE @N INT 
-- Total count of rows matching specified criterion
DECLARE @cnt decimal(18,2) 
-- No. of times DELETE statement should loop to delete all relevent records
DECLARE @loops INT 

SET @N = 1000000

SELECT @cnt = COUNT(*) FROM aagtrans 
    WHERE    (amount_1 =0.00 and amount_2 = 0.00 
            and amount_3 = 0.00 and amount_4 = 0.00)

SET @loops = CEILING(@cnt/@N)

WHILE @loops > 0
    BEGIN
        DELETE TOP (@N) FROM aagtrans 
        WHERE    (amount_1 =0.00 and amount_2 = 0.00 
                and amount_3 = 0.00 and amount_4 = 0.00)
        
        SET @loops = @loops - 1
    END

END
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: