Invalid Object Name after Creating a Table using a while loop

I created this loop to create and populate a temporary table for each int representing a day. The tables are created and properly executed but i get Invalid Object Name everytime i try to print a table by itself eg. #Day1. Any help will be appreciated thanks

declare @day int = 1
declare @dayMax int = 36
declare @dayString varchar(50) = 'Day'

while @day <= @dayMax
begin
    set @dayString = 'Day' + cast(@day as varchar(50))
    exec ('IF OBJECT_ID(''tempdb..#' + @dayString + ''') IS NOT NULL DROP TABLE #' + @dayString)
    exec ('select * into #' + @dayString + ' from #RenewalSMSCampaignReminderDays where DaysToRenewal = ' + @day)

    set @day = @day + 1
end

-- print table for Day 1
select * from #Day1

Temporary tables created in dynamic SQL are out of scope from the parent static SQL that executed the dynamic SQL. You either need to create the temporary tables ahead of time in the parent SQL code, which will be in scope to the dynamic SQL, or do all of your work including selecting from the temporary tables, in the dynamic SQL code itself.

What I find interesting is you're trying to create 36 temporary tables with the same schema of data, from another temporary table with the same schema. This is not a very manageable and probably less performant way to utilize the data from your #RenewalSMSCampaignReminderDays table. Why not just select from that temp table filtering on the DaysToRenewal column for whichever set of rows you need when you need it? You wouldn't even need dynamic SQL this way.

StackExchange Database Administrators Q#320830, answer score: 2