Home > Mobile >  Query to delete data before 90 days in 100-100 batches
Query to delete data before 90 days in 100-100 batches

Time:12-29

DECLARE @DELETED_ROWS INT;
SET @DELETED_ROWS = 100

WHILE (@DELETED_ROWS > 0)
BEGIN
    WITH cte AS
    (
         SELECT
             DATEADD(DAY, -90, UPDATEDDATE) AS date, audit_id 
         FROM
             auditdata 
    ) 
    DELETE TOP (100) 
    FROM cte 
    WHERE audit_id <= (SELECT MIN(audit_id)   100 FROM cte) 

    SET @DELETED_ROWS = @@ROWCOUNT
END

CodePudding user response:

So you're splitting the delete into batches.
Then you might want to commit the batch transactions.

And the delete statement can be simplified.

SET NOCOUNT ON;
 
DECLARE @DELETED_ROWS INT = 1;
 
WHILE @DELETED_ROWS > 0
BEGIN
  BEGIN TRANSACTION;
 
  DELETE TOP (100)
  FROM auditdata
  WHERE UPDATEDDATE < DATEADD(DAY, -90, GetDate());
 
  SET @DELETED_ROWS = @@ROWCOUNT;
 
  COMMIT TRANSACTION;
END;

CodePudding user response:

DECLARE @Rows INT = 100
WHILE (@Rows=100)
BEGIN
    DELETE TOP (100)
    FROM auditdata
    WHERE 
        UPDATEDDATE<DATEADD(DAY,-90, GETDATE())
    
     SET @Rows = @@ROWCOUNT
    
 END
  • Related