Export the dynamic query to a table

Question

I would like to export the results of a dynamic query with dynamic columns to a temporary table. Since the columns are dynamic I cannot pre-define the schema.

Tried using temporary table by using the Select.. Into however that does not work and no table is outputted.

declare @start DATETIME = (select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-13, 0))  
declare @end DATETIME = (select  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) 
declare @v_columns varchar(max)
declare @v_sql varchar(max)

;with months (v_date)
AS
(
    SELECT @start
    UNION ALL
    SELECT DATEADD(month,1,v_date)
    from months
    where DATEADD(month,1,v_date)<=@end
)
select @v_columns=stuff((select','+QUOTENAME(DATENAME(month,v_date)+cast(year(v_date) as varchar)) from months for xml path('')),1,1,'')
print @v_columns
set @v_sql='
;with months (v_date)
AS
(
    SELECT cast('''+convert(varchar(10),@start,101)+''' as date)
    UNION ALL
    SELECT DATEADD(month,1,v_date)
    from months
    where DATEADD(month,1,v_date)<='''+convert(varchar(10),@end,101)+'''
)

SELECT 
 INVOICECUSTOMERACCOUNTNUMBER, ORGANIZATIONNAME,'+@v_columns+'
INTO #newtbl FROM 
(

SELECT 
b.INVOICECUSTOMERACCOUNTNUMBER, c.ORGANIZATIONNAME,SUM(a.LINEAMOUNTMST) [LineAmount],DATENAME(month,a.invoicedate)+cast(year(a.invoicedate) as varchar) as v_date
from dbo.Table1 a

inner join dbo.Table2 b
on a.INVOICEID=b.INVOICENUMBER
inner join dbo.Table3 c 
on b.INVOICECUSTOMERACCOUNTNUMBER=c.CUSTOMERACCOUNT
group by b.INVOICECUSTOMERACCOUNTNUMBER,c.ORGANIZATIONNAME,a.INVOICEDATE) as x
PIVOT
(
 SUM(LineAmount) FOR v_date IN ('+@v_columns+')
) PVT; SELECT * FROM #newtbl'

print @v_sql

EXEC (@v_sql)

A table which is created dynamically with dynamic column names and its values.

Just curious, why do you need this into a temp table? If the table schema is dynamic you would have to use dynamic sql to do anything meaningful with the results anyway. What is the problem you are trying to solve? If we understand the problem we can probably help you find a better solution. — Sean Lange, Sep 03 '19 at 15:29
So the query looks at each customer account number and gives me the sums of the sales for the last 13 months from today's date. It is not necessary for it to be in a temp table. The ultimate aim is to then take this table into Power BI for users to view it and create visualisations etc. — Keval Shah, Sep 03 '19 at 15:59
Well you would have to constantly drop and recreate the table in Power BI because it could change every time it is run. Sounds like a more normalized structure would be more useful. — Sean Lange, Sep 03 '19 at 16:01
Duplicate on [msdn](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1c386600-1acc-44be-9603-9bcdebca69de/export-the-dynamic-query-to-a-table?forum=transactsql). — SMor, Sep 04 '19 at 13:57

score 0 · Answer 1 · answered Sep 03 '19 at 15:10

0

I believe your temp table is going out of scope. Instead of using a local temp table (a single #) try using a global temp table (two #s).

Review this question (and answer) for more background on local and global temp tables.

answered Sep 03 '19 at 15:10

Isaac

3,240
2
24
31

Hi Isaac, I tried that but just cannot see any temp tables and cannot see Temp Database in the object browser – Keval Shah Sep 03 '19 at 15:27
Temporary tables are stored in the **tempdb** database. You should be able to find it there under **Temporary Tables**. Why do you want to see it there? – Isaac Sep 03 '19 at 15:32
Yes this is what I mean, there is no Temporary Tables section in the Object Browser – Keval Shah Sep 03 '19 at 15:50
Could be a permissions issue. My question remains. Why do you want to see it there? – Isaac Sep 03 '19 at 15:55
It is not necessary to see it there, I just need the query to be output into a table which I will then use in Power BI. – Keval Shah Sep 03 '19 at 15:57
I am just not sure what the best way is to achieve that – Keval Shah Sep 03 '19 at 15:57
I would question whether a temporary table is right solution, but I don't have a better one for you. Sorry. – Isaac Sep 03 '19 at 16:03

score 0 · Answer 2 · answered Sep 03 '19 at 15:34

0

The temp table must exist before the dynamic sql in order for you to use the data. So you can define the temp table with the static columns first like:

CREATE TABLE #newtbl (
INVOICECUSTOMERACCOUNTNUMBER YourDataType,
ORGANIZATIONNAME YourDataType
);

Then build a dynamic SQL similar as @v_columns with data types to alter the table #newtbl.

Update your current dynamic sql to insert #tbl select

answered Sep 03 '19 at 15:34

PeterHe

2,766
1
8
7

You can build the ALTER TABLE command to add the dynamic columns, similar to select @v_columns=stuff((select','+QUOTENAME(DATENAME(month,v_date)+cast(year(v_date) as varchar) + ' nvarchar(64)) NULL ') from months for xml ;path('')),1,1,'') – PeterHe Sep 03 '19 at 15:59
SET @SQL='ALTER TABLE #newtb' + @v_columns; EXEC(@SQL); – PeterHe Sep 03 '19 at 15:59

Export the dynamic query to a table

2 Answers2