Monday, May 5, 2014

SQL Script to Delete Millions of Record

DECLARE @continue int
DECLARE @rowcount int
Declare @total BIGINT

Select @total = count(*) from Tablename WITH (NOLOCK) where condition
While @total>0
BEGIN
SET @continue = 1
WHILE @continue = 1
BEGIN
SET ROWCOUNT 10000

BEGIN TRANSACTION
Delete from Tablename where CONDITION
SET @rowcount = @@rowcount 
COMMIT
 
IF @rowcount = 0
BEGIN
SET @continue = 0
END
END
Set @total =@total-10000
END