Loop all records

Basically, there is another way of looping which uses CURSOR. But, it requires record locking which may not work as fast as you think.

In this article, we are going to use a temporary table to keep the data and then loop it with "seq" filter. The following example which is printing all table names on the screen. You may modify the PRINT line so that it compose the necessary SQL statement before you print it or execute any dynamic SQL.



set nocount on

declare
    @seq int
    , @name nvarchar(255)

-- declare a temporary table in the memory.
declare @tb table (
    seq int identity(1,1)
    , name nvarchar(255)
)

--copy all the necessary records to the temporary table '@tb'
insert into @tb (name)
select name
from sys.objects
where
    type = 'u'and
    name like 'tb_suggest%'
order by name

-- init the var before use
set @seq = 0


-- start the lopp
while exists(
    select *
    from @tb
    where seq > @seq
)
begin

    -- always load the first record that is greater than
    -- last seq.
    select top 1                --<<=== this is very important!!!
        @seq = seq
        , @name = name
    from @tb
    where seq > @seq
    order by seq                --<<=== this is very important!!!


    -- do whatevery necessary process here.
    -- you may use 'set xxx' to join the sql string before print it out.

    print '@name => ' + @name

end