Friday, 19 August 2011

SQL Delete in batches based on date

This is a sample how to delete data in batches to avoid large transaction log. This is a simpler version of this example when I used 2 tables.

DECLARE @MyTable TABLE 
( 
	Id int,
	DateUpdated DATETIME
)
INSERT INTO @MyTable
SELECT 1,DATEADD(d,-1,GETDATE())
UNION SELECT 2,DATEADD(d,-2,GETDATE())
UNION SELECT 3,DATEADD(d,1,GETDATE())
UNION SELECT 4,DATEADD(d,2,GETDATE())
UNION SELECT 5,DATEADD(d,-3,GETDATE())
UNION SELECT 6,DATEADD(d,-2,GETDATE())
UNION SELECT 7,DATEADD(d,-4,GETDATE())
DECLARE @date DATETIME
SET @date = GETDATE()
PRINT CONVERT(CHAR(19),GETDATE(),120) +' Deleting data in batches' 
WHILE (Exists(SELECT 1 FROM @MyTable WHERE DateUpdated < @date))
BEGIN TRY    
    BEGIN TRAN     
       PRINT CONVERT(CHAR(19),GETDATE(),120) +'   Deleting 2..'   
       DELETE TOP(2) @MyTable FROM @MyTable WHERE DateUpdated < @date
    COMMIT TRAN 
END TRY
BEGIN CATCH 
    ROLLBACK
END CATCH
--SELECT * FROM @MyTable

No comments:

Post a Comment