Delete a batch of rows from a table. Helpful when deleting large quantities of rows from a big table.

recursive_delete.sql

DECLARE @DT_ANOMES_REF INT        = 201702
DECLARE @QTD_REG_BATCH INT        = 0
DECLARE @ID INT                   = 0 

SET @QTD_REG_BATCH = CEILING((SELECT COUNT(*) FROM DB_PGC_PRODUCAO..TB_VIDA_DBM WHERE DT_ANOMES_REF = @DT_ANOMES_REF) / 100000)

WHILE @ID <= @QTD_REG_BATCH -- Este número resulta no calculo [Qtde. Linhas a serem deletadas] / [100000]
BEGIN
       ;WITH CTE AS
       (
             SELECT TOP 100000 *
               FROM DB_PGC_PRODUCAO..TB_VIDA_DBM
             WHERE DT_ANOMES_REF = @DT_ANOMES_REF
       )

       DELETE
         FROM CTE
       SET @ID = @ID + 1
END