Site icon Professional Auto Mechanic

Query to delete data before 90 days in 100-100 batches

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
Exit mobile version