Getting access to a dynamic table from dynamic sql

Good day StackOverflow

The table that I create from my dynamic sql can have any number of columns as it is a pivot table.

-- Pivot the table so we get the UW as columns rather than rows
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = '
SELECT *
FROM #PreProcessed
PIVOT (SUM(Quotes)
        FOR [UW] IN (' + @UWColumns + ')
        ) AS bob'

I run this code to run my dynamic sql.

EXEC sp_executesql @SQL,
                  N'@UWColumns nvarchar(MAX)',
                  @UWColumns

My question is, how do I store the resulting table? Especially when I don't know how many columns it will have or even what the columns will be called?

I tried the code below but it doesn't work

   INSERT INTO #Temp
   EXEC sp_executesql @SQL,
                  N'@UWColumns nvarchar(MAX)',
                  @UWColumns

Thanks everyone

Answers


SQL Server uses SELECT * INTO ...., as opposed to the CREATE TABLE AS syntax. So you'll need to modify your dynamic sql to:

 SELECT * INTO <YOUR TABLE>
    FROM #PreProcessed
    PIVOT (SUM(Quotes)
            FOR [UW] IN (' + @UWColumns + ')
            ) AS bob'

The only way I could find around this problem was to do all of my calculations in the dynamic sql. Which meant I had to work on two tables.

-- Pivot the table so we get the UW as columns rather than rows
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = '
SELECT * INTO #temp
FROM #PreProcessed
PIVOT (SUM(Quotes)
        FOR [UW] IN (' + @UWColumns + ')
        ) AS bob

SELECT DISTINCT t1.Date, d.Declines AS ''Declines'' , '+@UWColumns+'
FROM #temp AS t1 LEFT OUTER JOIN
#Declines AS d ON t1.DATE = d.DATE
'

PRINT @SQL

EXEC(@SQL)

Need Your Help

Merge json entries with d3

javascript json d3.js

I am making some visualisations and need to merge certain json entries.

How to check the existence of D and E drives in WIX?

wix wix3.5

I want to install my application into E drive (E:\MyApp) if not exists then D drive (D:\MyApp), if D drive also doesn't exist in the target computer then I want to install in C drive (C:\MyApp). Wh...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.