Удаление пачками по кластерному ключу пример

Tsql теория > Удаление пачками по кластерному ключу пример
09.10.2019 10:28:53



Статья:

DECLARE @idABS INT = (SELECT idType FROM [MDM_Light].dbo.drType WITH(readuncommitted) WHERE sCode = 'ABS_CRM_BMB')
CREATE TABLE #tmp12 (idobject VARCHAR(255),flag INT)
INSERT INTO #tmp12 (idobject) SELECT  h2.idobject FROM abs.bf_rl_subject_contact_phone k  
   INNER JOIN [ARCH_BF_MDM_Light].dbo.del_rl_subject_contact_tmp   h2 (nolock)    ON k.idAbs=@idABS AND h2.idObject=k.idObject AND h2.obj='phone'
   GROUP BY h2.idobject
CREATE nonclustered INDEX ix_table_tmp12 ON #tmp12 (idobject)
CREATE nonclustered INDEX ix_table_tmp123 ON #tmp12 (flag)
 
DECLARE @tmps2 TABLE (id VARCHAR(255))
WHILE 1=1
BEGIN
  DELETE FROM  @tmps2
  INSERT INTO @tmps2
		     SELECT top 1000  h.[idobject] FROM  #tmp12  h  
			  WHERE h.[flag] IS NULL
  IF @@ROWCOUNT = 0 BREAK
  -- соственно удаление
	DELETE w
	FROM abs.bf_rl_subject_contact_phone w
		 INNER JOIN @tmps2 e ON e.id = w.idobject
								AND w.idAbs = @idABS;
   -- пометим обработанные
		  UPDATE t
		   SET t.flag=1
		   FROM #tmp12 t
		   INNER JOIN @tmps2 h ON h.id=t.[idobject]
END