1、使用While...循环一个表
- 首先保证表有一个唯一值的列,注意只需要值唯一,不需要保证值连续;
- 如果列中存在重复的值,那么重复的值,只会有一个会被循环到;
declare @au_id char( 11 )
select @au_id = min( au_id ) from authors
while @au_id is not null
begin
select * from authors where au_id = @au_id
select @au_id = min( au_id ) from authors where au_id > @au_id
end
NOTE: Both example 1 and 2 assume that a unique identifier exists for each row in the source table. In some cases, no unique identifier may exist.
2、使用游标Cursor
declare @sql varchar(max)
declare @data_table varchar(50)
declare data cursor for
select data_table -- name of the table you have to query
from somewhere -- where these table names are stored
open data
fetch next from data into @data_table
while (@@fetch_status = 0)
begin
-- result_table = name of the table you want the results to be stored
-- control_table = name of the control table you mentioned
set @sql = 'insert into result_table
select *
from '+@data_table+'
where AccountId in (select AccountId
from control_table
where Appear_In_View = 1)
and Most_Recent_Data = 1'
exec(@sql)
fetch next from data into @data_table
end
close data
deallocate data