troubleshooting Question

SQL - Putting a dynamic SQL result INTO table

Avatar of csehz
csehzFlag for Hungary asked on
SQLMicrosoft SQL Server
3 Comments1 Solution17 ViewsLast Modified:
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,
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros