SQL - Putting a dynamic SQL result INTO table

Dear Experts,

I got a great solution on EE in the below example, however somehow being not able to put the result of the dynamic SQL query into a temp table.

CREATE TABLE
   #TempTable (
      CountryCode nvarchar(2),
      CountryName nvarchar(20),
      SearchTerm int,
      TaxNumber int,
      VAT int
      );


INSERT INTO
   #TempTable (CountryCode, CountryName, SearchTerm, TaxNumber, VAT)
VALUES
   ('AD', 'Andorra', 2, 3, 6),
   ('AL', 'Albania', 22, 32, 6),
   ('AM', 'Armenia', 24, 3, 66),
   ('AO', 'Angola', 11, 5, 9)


declare @sql nvarchar(max), @cols nvarchar(max)


select
@cols = Coalesce(@cols + ', ', '') + '[' + [name] + ']'
from tempdb.sys.columns
where object_id = Object_id('tempdb..#TempTable') and column_id >= 3
order by column_id


set @sql = '
Select CountryCode, CountryName, SAPFieldType, SAPField
into #ResultTempTable
from #TempTable
unpivot
(
    SAPField for SAPFieldType in
    (
        '+@cols+'
    )
) up
Order By SAPFieldType
'


exec (@sql)


SELECT * FROM #ResultTempTable

Select allOpen in new window

Briefly the issue is that inspite of adding the line:

into #ResultTempTable

Select allOpen in new window

somehow the query not showing any result:

SELECT * FROM #ResultTempTable

Select allOpen in new window

rather an error message that it does not exist:



If that into #ResultTempTable row is deleted, then the dynamic SQL result works fine bringing to the screen, but so the target would be put into a table

Thanks in advance,