sql server - SQL iterative joining within while loop -

i know it's not best practice apply while loop within sql code, can't imagine way around it.

i have ever-growing set of historical tables want summarize, , join, using common id. of these historical tables have matching structure.

the table names contain month , year; iterate through tables found between 2 periods - determined using fixed timestamp @startdate, , timestamp @enddate via getdate().

the while loop intended continually append additional columns each historic file. there 1 month per iteration, , each column contains pertaining month's summary.

the problem is, every left join seems erase there in previous iteration. use conditional statement determine if temp table exists, , build using select ... into statement if doesn't, , insert into statement if does.

the following have:

while @monthcount > 0     begin         select @month = datesuffix @datetable row = @monthcount         select @table = table_name information_schema.tables table_name concat('usa_rethhds_%', @month)         if @table not null             begin                 if object_id('tempdb.dbo.#rethhds', 'u') not null                     drop table #rethhds                 create table #rethhds                     (buc_cd varchar(12),                     ret_bkd_hh_count int)                 select @table = concat('dbo.', @table)             set @sql =                 'insert #rethhds                  select buc_cd, count(distinct sk_hh_id) ret_bkd_hh_count ' + @table +                 ' group buc_cd'             execute sp_executesql @sql;             if object_id('tempdb.dbo.#historical_table', 'u') null                 begin                     select #usa_branchcatalogue.tr, #usa_branchcatalogue.name, #rethhds.ret_bkd_hh_count #historical_table                     #usa_branchcatalogue                     left join #rethhds                     on #usa_branchcatalogue.tr = #rethhds.buc_cd                 end             else                 begin                     insert #historical_table                     select #usa_branchcatalogue.tr, #usa_branchcatalogue.name, #rethhds.ret_bkd_hh_count                     #usa_branchcatalogue                     left join #rethhds                     on #usa_branchcatalogue.tr = #rethhds.buc_cd                 end             select * #historical_table         end      select @monthcount = @monthcount - 1 end 

as loop through, #historical_table not grow additional rightmost columns. instead, remains columns tr, name, , ret_bkd_hh_count.

bonus points if there's easy way append corresponding month ret_bkd_hh_count field. however, when following,

set @sql = 'create table #rethhds (buc_cd varchar(12), ret_bkd_hh_count_' + @month + ' int)' execute sp_executesql @sql; 

i receive following message:

invalid object name '#rethhds'.