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 @MyTableSELECT 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 DATETIMESET @date = GETDATE()PRINT CONVERT(CHAR(19),GETDATE(),120) +' Deleting data in batches'WHILE (Exists(SELECT 1 FROM @MyTable WHERE DateUpdated < @date))BEGIN TRYBEGIN TRANPRINT CONVERT(CHAR(19),GETDATE(),120) +' Deleting 2..'DELETE TOP(2) @MyTable FROM @MyTable WHERE DateUpdated < @dateCOMMIT TRANEND TRYBEGIN CATCHROLLBACKEND CATCH--SELECT * FROM @MyTable
Comments
Post a Comment