CODEWITHSUNDEEP
sql-servert-sql
Grim Rieber
Grim Rieber

Reputation: 9

Select * into Temp Table from Dynamic SQL Results

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)

Upvotes: 0

Views: 13071

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81930

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

Upvotes: 4

Gopakumar N.Kurup
Gopakumar N.Kurup

Reputation: 936

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

INSERT into #TempCostsFinal 
Execute(@query)

Upvotes: 1

Related Questions