Insert query results into temp table

Question

I've recently learned from here how to do PIVOT in SQL, and I actually took an example from the other question on here. It works perfectly.

However, I want to perform additional joins, after the query, but I am unable to insert into temporary table the results of query? How may I do that?

Create table

CREATE TABLE yt
([Store] int, [Week] int, [xCount] int)
;
INSERT INTO yt
([Store], [Week], [xCount])
VALUES
(102, 1, 96),
(101, 1, 138),
(105, 1, 37),
(109, 1, 59),
(101, 2, 282),
(102, 2, 212),
(105, 2, 78),
(109, 2, 97),
(105, 3, 60),
(102, 3, 123),
(101, 3, 220),
(109, 3, 87);

Perform pivoting query

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Week) 
                from yt
                group by Week
                order by Week
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')


set @query = 'SELECT store,' + @cols + ' from 
         (
            select store, week, xCount
            from yt
        ) x
        pivot 
        (
            sum(xCount)
            for week in (' + @cols + ')
        ) p '


execute(@query)

The result is

store   1   2   3
101 138 282 220
102 96  212 123
105 37  78  60
109 59  97  87

But Id like to have it in #temp table, and I tried placing INTO #temp before 'Execute Query' and before FROM statement within Query.

Any idea? I am aware of SELECT * INTO #temp FROM BlaBla but seems its diff with Queries.

This might be helpful: http://stackoverflow.com/questions/14622940/insert-into-table-exec-query — jpw, Aug 19 '15 at 14:44

score 0 · Answer 1 · answered Aug 19 '15 at 14:48

0

The problem you're facing with select ... into #temp inside the execute is that the table gets created, but due to being in separate scope, it gets dropped immediately when the execute ends, so your procedure can't see it.

Your code will work, if you create the table before calling execute and just use insert into. You can check this in SQL Fiddle. This just causes the problem that if your query is dynamic, how to create the table so that it fits the query.

Trying to create logic where you dynamically adjust the number of columns in SQL is not simple to do, and really isn't something you should be doing. Better way would be to handle that in your presentation layer.

answered Aug 19 '15 at 14:48

James Z

12,209
10
24
44

The result set has a dynamic set of columns, that's why the OP is using dynamic sql to do the pivotting. How would you create a dynamically defined table without that table going out of scope? *(**I** wouldn't, this is generally a code-smell, needing dynamic sql to pivot a table suggests to me th the table should be kept in it's normalised form.)* – MatBailie Aug 19 '15 at 15:01
@MatBailie Yes exactly, that's what I tried to say. – James Z Aug 19 '15 at 15:25

Sam Cohen-Devries · Accepted Answer · 2015-08-19T15:55:57.483

0

You can create a global temp table dynamically

  declare @tblName nvarchar(10)
  set @tblName = N'##Temp' + cast(@@spid as nvarchar(5))
  declare @tblCreate nvarchar(max)
  SET @tblCreate = N'create table ' + @tblName + ' (' 
  + REPLACE(@cols,',',' int,') + N' int)'

  EXECUTE sp_executesql @tblCreate

And then edit your @query to insert into the table.

set @query = 'INSERT INTO ' + @tblName + ' SELECT store,' + @cols + ' from 
         (
            select store, week, xCount
            from yt
        ) x
        pivot 
        (
            sum(xCount)
            for week in (' + @cols + ')
        ) p; 
        drop table ' + @tblName


execute(@query)

edited Aug 19 '15 at 15:55

answered Aug 19 '15 at 15:07

Sam Cohen-Devries

2,025
2
18
35

@MatBailie A global temp table will remain in scope – Sam Cohen-Devries Aug 19 '15 at 15:35
http://stackoverflow.com/questions/2920836/local-and-global-temporary-tables-in-sql-server – Sam Cohen-Devries Aug 19 '15 at 15:36
Sorry, didn't notice the `##`, my apologies. It should be noted then that all users/sessions will see this table and to concurrency can become an issue. – MatBailie Aug 19 '15 at 15:38
@MatBailie that is true. ##Temp would probably not be the best name for it, something more unique would be better – Sam Cohen-Devries Aug 19 '15 at 15:43
1

Concurrency would be an issue regardless. Two users running this SP at the same time will both attempt to create and utilise the a global temporary table, but with the same name and potentially different definitions. Perhaps use the @@spid in the name? – MatBailie Aug 19 '15 at 15:45

score 0 · Answer 3 · answered May 27 '22 at 11:09

0

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Week) from yt group by Week order by Week FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')

set @query = 'SELECT store,' + @cols + ' Into ##tempyt from ( select store, week, xCount from yt ) x pivot ( sum(xCount) for week in (' + @cols + ') ) p '

DROP TABLE IF EXISTS ##tempyt;

execute(@query);

select * from TempDB.sys.##tempyt

answered May 27 '22 at 11:09

Madhavi G

1
2

I got a similar issue, I used the above approach it got resolved. – Madhavi G May 27 '22 at 11:12
It tends to be a most helpful response when the code given as the answer is adequately explained or commented on. Now, this doesn't apply if the code is self-explainatory due to function/call names, variable names supplied, etc. Your answer will be more valuable to everyone if inserted some details of what you're trying to accomplish with each line of code. SQL can be an especially tricky language to get a decent understanding for some ppl, so including links to the docs for the specific arrangement of the command along with an explanation of your thoughts or theory of execution will give tho – Kingsolmn May 27 '22 at 23:09

Insert query results into temp table

3 Answers3