Inserting the results set of SQL Dynamic Pivot Query into Temporary Table

Question

How can I insert into global temporary table the results of the dynamic t-sql query in which columns is not fixed. See below for the table definition, value insertion and the t-sql for pivot query.

/****** Object:  Table [dbo].[ProdOrders]    Script Date: 30/8/2017 7:24:35 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ProdOrders](
    [ItemCode] [nvarchar](50) NULL,
    [ReleasedDate] [date] NULL,
    [PlanQty] [float] NULL,
    [ActualQty] [float] NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[ProdOrders]
           ([ItemCode]
           ,[ReleasedDate]
           ,[PlanQty]
           ,[ActualQty])
     VALUES
    ('0D203-22882-079','2017-08-18',12654,4218),
    ('0D203-22882-079','2017-08-23',15758,5253),
    ('0D203-22882-079','2017-08-27',26263,8754),
    ('0D203-22882-079','2017-09-02',7354,2451),
    ('0D301-05422-079','2017-08-18',31144,10381),
    ('0D301-05422-079','2017-08-18',20612,6871),
    ('0D301-05422-079','2017-08-23',25765,8588),
    ('0D301-05422-079','2017-08-29',19582,6527),
    ('0D301-05422-079','2017-09-04',15459,5153),
    ('0D203-22882-079','2017-09-22',5232,1744),
    ('0D203-22882-079','2017-09-28',13236,4412),
    ('0D203-22882-079','2017-10-03',7693,2564),
    ('0D301-05422-079','2017-09-23',24735,8245),
    ('0D301-05422-079','2017-09-27',19561,6520),
    ('0D301-05422-079','2017-09-06',23755,7918),
    ('0D301-05422-079','2017-09-14',23755,7918),
    ('0D301-05422-079','2017-09-17',29694,9898),
    ('0D203-22882-079','2017-11-01',2263,754),
    ('0D203-22882-079','2017-10-21',15693,5231),
    ('0D203-22882-079','2017-10-20',15968,5323),
    ('0D203-22882-079','2017-10-25',10521,3507),
    ('0D301-05422-079','2017-10-21',23755,7918),
    ('0D301-05422-079','2017-10-29',17816,5939),
    ('0D301-05422-079','2017-11-01',15612,5204),
    ('0D301-05422-079','2017-10-03',20816,6939),
    ('0D301-05422-079','2017-10-11',15612,5204),
    ('0D301-05422-079','2017-10-18',26020,8673)

Declare @SQL varchar(max) = '
Select *
 From (
        Select A.ItemCode
              ,B.*
         From  [dbo].[ProdOrders] A
         Cross Apply ( values ( convert(varchar(6),ReleasedDate,112)+''-Plan'',PlanQty)
                             ,( convert(varchar(6),ReleasedDate,112)+''-Actual'',ActualQty)
                     ) B (Item,Value)
      ) S
 Pivot (sum([Value]) For [Item] in (' + Stuff((Select Distinct ','+QuoteName(convert(varchar(6),ReleasedDate,112)+'-Plan') 
                                                              +','+QuoteName(convert(varchar(6),ReleasedDate,112)+'-Actual') 
                                               From [dbo].[ProdOrders]
                                               Order By 1 
                                               For XML Path('')),1,1,'')  + ') ) p'
Exec(@SQL);

I need to insert the results sets to global temporary table so that I can use it to refer with another query.

Be very careful here...global temp tables are an indication that something is wrong with your approach to the problem. They are fraught with concurrency issues and cause most people lots of problems. — Sean Lange, Aug 30 '17 at 13:16

score 1 · Answer 1 · edited Aug 30 '17 at 13:57

1

Add into clause in your select statement and try

Declare @SQL varchar(max) = '
Select *
into ##globaltemptable
From ( ...

edited Aug 30 '17 at 13:57

shA.t

16,580
5
54
111

answered Aug 30 '17 at 13:04

arunbabu

279
3
15

score 1 · Accepted Answer · answered Aug 30 '17 at 14:02

The script below creates dynamically a global temp table. This script does not have a check to see if the table allready exists, you have to add that yourself. Be carefull with using global temp tables. I think there are better alternatives to what you want.

    --Script for creating global temp table
    Declare @CreateTempTable [nvarchar](max) = '
    CREATE TABLE ##t ([ItemCode] nvarchar(50),' +
    stuff((Select distinct ',' + QuoteName(convert(varchar(6), ReleasedDate,112)+'-Plan') + ' float,' + QuoteName(convert(varchar(6),ReleasedDate,112)+'-Actual')  + '  float'
    From [dbo].[ProdOrders]
    Order By 1 For XML Path('')),1,1, '') + ')'

    Exec sp_executesql @CreateTempTable

    --Script for insert
    Declare @InsertSql [nvarchar](max) = '
    INSERT INTO ##t ([ItemCode], ' +
    stuff((Select distinct ','+QuoteName(convert(varchar(6), ReleasedDate,112)+'-Plan') + ', ' + QuoteName(convert(varchar(6),ReleasedDate,112)+'-Actual')  + ' '
    From [dbo].[ProdOrders]
    Order By 1 For XML Path('')),1,1, '') + ')'

    Declare @SQL varchar(max) = @InsertSql + '
    Select *
     From (
            Select A.ItemCode
                  ,B.*
             From  [dbo].[ProdOrders] A
             Cross Apply ( values ( convert(varchar(6),ReleasedDate,112)+''-Plan'',PlanQty)
                                 ,( convert(varchar(6),ReleasedDate,112)+''-Actual'',ActualQty)
                         ) B (Item,Value)
          ) S
     Pivot (sum([Value]) For [Item] in (' + Stuff((Select Distinct ','+QuoteName(convert(varchar(6),ReleasedDate,112)+'-Plan') 
                                                                  +','+QuoteName(convert(varchar(6),ReleasedDate,112)+'-Actual') 
                                                   From [dbo].[ProdOrders]
                                                   Order By 1 
                                                   For XML Path('')),1,1,'')  + ') ) p'
    Exec(@SQL);

    Select * From ##t

Inserting the results set of SQL Dynamic Pivot Query into Temporary Table

2 Answers2