update пачками по 100 шт записей

Tsql теория > update пачками по 100 шт записей
10.12.2018 21:30:51


Наиболее часто встречающиеся слова в статье:

[varchar] [declare] [''Country_Dummy1''] [''Country_Dummy2''] [''Country_Dummy3''] [OBJECT_ID] [''tempdb] [default] [sCode=h] [ROWCOUNT]


Статья:

declare @t table (sCode varchar(50))
insert into @t (sCode)
select ''Country_Dummy1''
union select ''Country_Dummy2''
union select ''Country_Dummy3''
 
if (select OBJECT_ID(''tempdb..#tmp'')) is not null drop table #tmp
 create   table #tmp (
    [idType] [varchar](100) NULL,
 flag int default 0)
insert into #tmp ([idType] )
 select h.[idtype] from drType h (nolock)
    inner join @t t on t.sCode=h.sCode
 
declare @tmps2 table (id varchar(100))
WHILE 1=1
   BEGIN
    delete from  @tmps2
     insert into @tmps2
       select top 100  h.[idType] from #tmp h 
     where h.[flag] =0
          IF @@ROWCOUNT = 0 BREAK
         update t
    set t.dclose=getdate()
    from drType t
    inner join @tmps2 h on h.[id]=t.[idType]
         -- пометим обработанные
    update t
     set t.flag=1
     from #tmp t
     inner join @tmps2 h on h.id=t.[idtype]
 
 end