Hi Joe,
Your problem is #tmptbl2 is only present in the scope of the SQL executed via EXEC (@SQL1-4), and is out of scope when you try to select from it.
You can create the temp table outside this & it will be available to the dynamic & fixed SQL.
Something like this:
IF object_id('tempdb..#tmptbl1') Is not null
drop table #tmptbl1
IF object_id('tempdb..#tmptbl2') Is not null
drop table #tmptbl2
create table #tmptbl2 (table_name varchar(100), AO_Counts INT, AE_Counts INT...
-- load yes information into temp tbl
select ltrim(rtrim(table_name))as table_name,
case when AO = 'Y' Then 'AO' end as AO,
case when AE = 'Y' Then 'AE' end as AE,
case when AR = 'Y' Then 'AR' end as AR,
case when NG = 'Y' Then 'NG' end as NG
into #tmptbl1
from stage.dbo.itapdb_ctrl_t
where AO = 'Y' or AE = 'Y' or AR = 'Y' or NG = 'Y'
Rewrite your Dynamic bits to:
Select @SQL = '
insert into #tmptbl2 (table_name, AO_Counts)
select '''+@tablename+''' as table_name, count (*) as AO_Counts
FROM pre_itap.dbo.'+@tablename+' where ssn in (select a.ssn
from pre_itap.dbo.person_t a, pre_itap.dbo.soldr_t b
where a.total_army_comp_cd = ''A''
and b.mil_pers_clas_cd = ''O'')'
Select @SQL2 = '
insert into #tmptbl2 (table_name, AE_Counts)
select '''+@tablename+''' as table_name, count (*) as AE_Counts
FROM pre_itap.dbo.'+@tablename+' where ssn in (select a.ssn
from pre_itap.dbo.person_t a, pre_itap.dbo.soldr_t b
where a.total_army_comp_cd = ''A''
and b.mil_pers_clas_cd = ''E'')'
etc
Then at the end, select * from #tmptbl2 should return results.
Hope that makes sense & note I've split AO, AE counts into seperate fields. You'll need to rework this to the format you require.
Thanks
Gaz