troubleshooting Question

SQL - Putting a dynamic SQL result INTO table

Avatar of csehz
csehzFlag for Hungary asked on
Microsoft SQL ServerSQL
3 Comments1 Solution16 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.

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
Briefly the issue is that inspite of adding the line:

into #ResultTempTable
somehow the query not showing any result:
SELECT * FROM #ResultTempTable
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 the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
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