How to create temp table with dynamic SQL query result

Question

I have this stored procedure:

Declare @MarketID AS NVARCHAR(MAX) = '1.136529848';
Declare @UserID AS NVARCHAR(MAX) = '6a309d84-d1c6-434d-b9df-4f96a74da912';

DECLARE @colsSelect AS NVARCHAR(MAX);
DECLARE @colsTemp AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

SELECT
    @colsSelect = STUFF((SELECT distinct ',' +
                    '''''' + ' as ' + QUOTENAME(name) 
                         FROM RunnersInfoes  AS t 
                         WHERE marketID = @MarketID 
                         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') , 1, 1, '');

PRINT @colsSelect

SET @query= ';WITH cte AS
             (
                  SELECT
                      id, ParentId, 0 AS Level, Share, AccountTypeName, FirstName
                  FROM
                      dbo.View_UserProfile 
                  WHERE
                      View_UserProfile.id = ' + '''' + @UserID + '''' +'
                  UNION ALL
                  SELECT
                      t.id, t.ParentId, Level + 1 AS Level, t.Share, t.AccountTypeName, t.FirstName
                  FROM
                      View_UserProfile t  
                  INNER JOIN
                      cte ON t.ParentId = cte.id
             )
             SELECT 
                 ID, AccountTypeName AS Type, FirstName AS Name, ' + @colsSelect + '      
             FROM cte AS t'

EXECUTE (@query)

and it's generating this result:

I want to create temp table or variable type table for following result , remember the column of this result are dynamically rendered. Sometimes result returns more columns and sometimes with less but first 3 columns remain the same for every result. So kindly help for creating dynamic table inside the stored procedure.

where ? i mean where i should enter select ... into ... from.. statement in this code ? — Fraz Zaki
– Fraz Zaki, Commented Nov 16, 2017 at 12:01
@GuidoG but my result comes with dynamic column , its not remain same with every param — Fraz Zaki
– Fraz Zaki, Commented Nov 16, 2017 at 12:04

HoneyBadger · Accepted Answer · 2017-11-16 12:07:51Z

6

You can do:

SELECT ID
,      AccountTypeName AS Type
,      FirstName AS Name
,      ' + @colsSelect + ' 
INTO   ##TEMPTABLE     
FROM   cte AS t

Since you execute this dynamically, you cannot use #TEMPTABLE because a local temp table will only exist in the scope of the query that defines it. Using ## creates a global temp table which will be accessible outside the scope of the dynamic query.

answered Nov 16, 2017 at 12:07

HoneyBadger

15.2k3 gold badges36 silver badges50 bronze badges

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

Comments

Prijesh Meppayil · Accepted Answer · 2017-11-16 12:08:09Z

0

Please use the SELECT - INTO clause for your use case as given below

SELECT * INTO #temptable FROM cte

answered Nov 16, 2017 at 12:08

Prijesh Meppayil

5403 silver badges17 bronze badges

Comments

GuidoG · Accepted Answer · 2017-11-16 12:27:36Z

0

To create a temp table that is filled by a dynamic query, use global temp tables like this example.
For the select ... into ... statement to work, you need to make sure every column from the select has a name.

declare @query varchar(1000) = 'select 1 as ID, ''test'' as Column_1 into ##mytable'

exec (@Query)

select * from ##mytable

drop table ##mytable

Do not forget to drop the temp table when your done.

edited Nov 16, 2017 at 12:27

answered Nov 16, 2017 at 12:22

GuidoG

12.3k6 gold badges59 silver badges102 bronze badges

Collectives™ on Stack Overflow

How to create temp table with dynamic SQL query result

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related