Select * into Temp Table from Dynamic SQL Results

Question

I wrote a dynamic pivot and need to input the results into a temp table to join the results to another table.

Perhaps there is a better way to get the results I want without inserting into a Temp table? Could I join a table inside the Dynamic Pivot to get the final result I want?

Getting the errors:

Msg 263, Level 16, State 1, Line 59
Must specify table to select from.

Msg 1038, Level 15, State 5, Line 59
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

My code is the following:

 declare @RealYear as nvarchar(max)
select @RealYear= '2016'
IF OBJECT_ID('tempdb.dbo.#TempCosts', 'U') IS NOT NULL
  DROP TABLE #TempCosts; 

IF OBJECT_ID('tempdb.dbo.#TempCostsFinal', 'U') IS NOT NULL
  DROP TABLE #TempCostsFinal; 

select * into #TempCosts from
(
--Journal Entries
select * from lth.dbo.vwJournalEntries_LTWS1
union all
--Non Journal Entries
select *,'' from lth.dbo.vwweeklydebits_ltws1
)D

--select * from #TempCosts
DECLARE @cols AS NVARCHAR(MAX)
declare  @query  AS NVARCHAR(MAX)
declare @temptable as nvarchar(max)

select @cols = STUFF((SELECT ',',  QUOTENAME(AccountRef_FullName) 
  from #TempCosts
                    group by AccountRef_FullName
                    having accountref_Fullname is not null
                    order by AccountRef_FullName
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT *,  ' + @cols + ' from 
             (
                select *
                from #TempCosts
                where year = ' + @RealYear + '
            ) x
            pivot 
            (
                sum(Amount)
                for AccountRef_FullName in (' + @cols + ')
            ) p 

        '


        execute(@query)


    select * into #TempCostsFinal Execute(@query)

John Cappelletti · Accepted Answer · 2017-01-04 15:30:24Z

4

It is risky because you run the risk of collisions, and not very efficient, but you can drop the dynamic results into a ##TempPivot

Set @query = '

IF OBJECT_ID(''tempdb..##TempPivot'') IS NOT NULL
  DROP TABLE ##TempPivot


SELECT *,' + @cols + ' 
 Into ##TempPivot
 From (
        select *
        from #TempCosts
        where year = ' + @RealYear + '
       ) x
    pivot 
    (
        sum(Amount)
        for AccountRef_FullName in (' + @cols + ')
    ) p 
'
Exec(@query)

Select * Into #Temp from ##TempPivot

Drop Table ##TempPivot

answered Jan 4, 2017 at 15:30

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gopakumar N.Kurup · Accepted Answer · 2017-01-04 06:35:29Z

1

Create the temp table with proper design. Then instead of SELECT * INTO use the below query

INSERT into #TempCostsFinal 
Execute(@query)

answered Jan 4, 2017 at 6:35

Gopakumar N.Kurup

9465 silver badges20 bronze badges

2 Comments

Grim Rieber Over a year ago

Due to the Dynamic Pivot the Temp Table will have dynamic columns that I will not know until the query has been executed.

Gopakumar N.Kurup Over a year ago

@GrimRieber See the answer from John. Seems that it is a good option. Using either a Global temp table or a "permanent temporary" table. Don't forget to drop the table (if it is existing) before the executing the query.

Collectives™ on Stack Overflow

Select * into Temp Table from Dynamic SQL Results

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related