Tuesday, 9 June 2009

SQL Delete in batches

This is a sample of how to delete a large chunk of data in batches. This is to avoid putting to much data into transaction log which can cause long rollback in case of failure.

DECLARE @Table1 TABLE(
id int
)

DECLARE @Table2 TABLE(
id int
)

INSERT INTO @Table1
SELECT 1
UNION SELECT 2
UNION SELECT 3

INSERT INTO @Table2
SELECT 1
UNION SELECT 2
UNION SELECT 4

SELECT * FROM @Table1

DECLARE @BatchSize int
SET @BatchSize = 10

WHILE (Exists(SELECT 1 FROM @Table1 t1 LEFT JOIN @Table2 t2 ON t1.Id = t2.Id WHERE t2.Id IS NULL ))
BEGIN TRY
BEGIN TRAN
PRINT 'Deleting'
DELETE TOP(@BatchSize) @Table1 FROM @Table1 t1 LEFT JOIN @Table2 t2 ON t1.Id = t2.Id WHERE t2.Id IS NULL
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK
END CATCH

SELECT * FROM @Table1

No comments:

Post a Comment