Delete records in batch using TOP clause and @@ROWCOUNT


How T-SQL can be used to delete millions of records in batch from the table. Here we go…

SET NOCOUNT ON
DECLARE @tblRecords TABLE(RowID INT )

INSERT  @tblRecords
SELECT  ROW_NUMBER() OVER(ORDER BY A.name)FROM sys.objects A CROSS JOIN sys.objects B
PRINT '# of Records added ---- ' + CAST(@@ROWCOUNT AS VARCHAR(10))

DECLARE @i INT
WHILE 1 = 1 BEGIN
      DELETE TOP(1000) ---define the batch size
      FROM @tblRecords

      SET @i = @@ROWCOUNT

      IF @i = 0 BREAK
      PRINT '# of Records deleted in batch ---- ' + CAST(@i AS VARCHAR(10))
END
GO
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