Avatar of csehz
Flag for Hungary

asked on 

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.

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

   #TempTable (CountryCode, CountryName, SearchTerm, TaxNumber, VAT)
   ('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)

@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
    SAPField for SAPFieldType in
) up
Order By SAPFieldType

exec (@sql)

SELECT * FROM #ResultTempTable

Open in new window

Briefly the issue is that inspite of adding the line:

into #ResultTempTable

Open in new window

somehow the query not showing any result:
SELECT * FROM #ResultTempTable

Open 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,
SQLMicrosoft SQL Server

Avatar of undefined
Last Comment

8/22/2022 - Mon