Click here to Skip to main content
16,018,158 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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. I need to insert the results sets to global temporary table so that I can use it to refer with another query.

What I have tried:

SQL
/****** 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);
Posted
Updated 1-Sep-17 7:45am

1 solution

Hi,

You can dynamically create the global temp table as well. The following codes should be inside your dynamic variable (@SQL)

1. Check if the global temporary table exists, if it does then drop it, because the columns might not match when inserting the data

if object_id(''tempdb..##TempTable'') is not null
begin
    drop table ##TempTable
end



2. Create the global temporary table with the same "Stuff" code you are using in the pivot table

create table ##TempTable([ItemCode] nvarchar(50) null, ' + Stuff((Select Distinct ','+QuoteName(convert(varchar(6),ReleasedDate,112)+'-Plan') + ' float null'
                                                              +','+QuoteName(convert(varchar(6),ReleasedDate,112)+'-Actual') + ' float null'
                                               From [dbo].[ProdOrders]
                                               Order By 1 
                                               For XML Path('')),1,1,'')+ ')


3. Add the insert statement to the select query you already have

INSERT INTO ##TempTable
Select *
 From...


4. Execute the query and the just do a select to the global temporary table

The final query should look something like this:

Declare @SQL varchar(max) = '

if object_id(''tempdb..##TempTable'') is not null
begin
    drop table ##TempTable
end

create table ##TempTable([ItemCode] nvarchar(50) null, ' + Stuff((Select Distinct ','+QuoteName(convert(varchar(6),ReleasedDate,112)+'-Plan') + ' float null'
                                                              +','+QuoteName(convert(varchar(6),ReleasedDate,112)+'-Actual') + ' float null'
                                               From [dbo].[ProdOrders]
                                               Order By 1 
                                               For XML Path('')),1,1,'')+ ')
INSERT INTO ##TempTable
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 ##TempTable


Hope this helps.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900