How to loop through tables dynamically and INSERT INTO a temporary table in MSSQL

To avoid a union, you can loop using a cursor. So, first you define your cursor that will list all the tables you want to query (let's assume you have all your data tables listed somewhere (system tables or config)). And then loop doing :

create a dynamic SQL statement with the name of the table you retrieved execute this statement So something like:

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
EDIT : with new inputs from Ariel Hayoun Try this kind of where clause if (and only if) there is one line te retrieve:
set @sql = 'insert into result_table select * from '+@data_table+' where AccountId in (select AccountId from control_table where Appear_In_View = 1) and Retrieved_At = (select max(Retrieved At) from '+@data_table +')'
If you have multiple lines per tables (one line per account id for instance), you may be able to achieve what you want using row_number() function

Here are the pros and cons of Using SQL Server loop through table:

1. While loops are faster than cursors.
   
2. While loops use less locks than cursors.
   
3. Less usage of Tempdb: While loops don't create a copy of data in tempdb as a cursor does.