DECLARE @TopRowsToDelete AS INT
SET @Deleted_Rows = 1
SET @TopRowsToDelete = 5000
/* 1. CRIA TMP COM INDICES*/
CREATE TABLE #xcci_log_tmp
(
ID int,
LOGICENTITY nvarchar(64)
)
CREATE NONCLUSTERED INDEX #IDX_CC_xcci_log_tmp ON #xcci_log_tmp (ID)
/* 2. PREENCHE TMP COM CHAVE*/
SELECT [ID]
,[LOGICENTITY]
INTO #xcci_log_tmp
FROM [SIRC]..[OSUSR_YAA_S_LOG] WITH (NOLOCK) WHERE [LOGICENTITY] = 'XCCI' AND [DETAIL] = 'Actualização de cliente em XCCI'
/* 3. CICLO DE DELETE DE 5000 DE CADA VEZ*/
WHILE (@Deleted_Rows > 0)
BEGIN
BEGIN TRANSACTION
DELETE TOP(@TopRowsToDelete) log FROM [SIRC]..[OSUSR_YAA_S_LOG] log INNER JOIN #xcci_log_tmp tmp ON log.ID=tmp.ID
SET @Deleted_Rows = @@ROWCOUNT
COMMIT TRANSACTION
END
/* 4. DESTROI TMP */
DROP TABLE #xcci_log_tmp
