Inserting Rows Into A Temp Table Created By An Execute Statement

Below is a simplified table & dataset to illustrate a problem I'm experiencing with a more complex one.





Code Block

create table #test(
recno smallint PRIMARY KEY,
value decimal (18,2))

insert into #test values (1, 3.57)
insert into #test values (2, 5.32)
insert into #test values (3,6.29)
insert into #test values (4, 9.25)
insert into #test values (5, 0.84)

Method 1: I tried inserting rows from #test into a temp table (#table) as follows




Code Block

declare @n as nvarchar(3)
set @n = 1
while @n <= (select count(recno) from #test)
begin
exec ('
insert into ##table
select *,
originalrecno = (select recno from #test where recno = '+@n+')
from #test'
)
set @n = @n + 1
end
However, this yields an error message:




Code Block

Server: Msg 208, Level 16, State 1, Line 2
Invalid object name '##table'.
Note - you can comment out the insert into ##table line above to view the results that I'm trying to put into ##table.


Method 2: next I tried explicitly creating ##table & rerunning the loop containing the insert




Code Block

create table ##table (
recno smallint,
value decimal (18,2),
originalrecno smallint)

declare @n as nvarchar(3)
set @n = 1
while @n <= (select count(recno) from #test)
begin
exec ('
insert into ##table
select *,
originalrecno = (select recno from #test where recno = '+@n+')
from #test'
)
set @n = @n + 1
end
This worked - it inserted the data from the select statements in the loop into ##table.

Question - why won't method 1 work?