Using dynamic SQL to create a table

Question

The following works:

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

SET @cols = 
STUFF
    (
        (
        SELECT   ',' + QUOTENAME(b."NewName")
        FROM    (
                    SELECT  myKey, 
                        win = SUM(win)
                    FROM    xxx.dbo.yyy
                    GROUP BY myKey
                    ) x
                    INNER JOIN #NameSwitch b ON
                        x.myKey = b.myKey
        ORDER BY x.win DESC
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'');


SET @query =    
    'SELECT [Measure],' + @cols + ' 
    FROM  
        (
            SELECT  [NewName], [Measure], [Amount]
            FROM    #UnpivottedData x
        ) x
        PIVOT 
        (
            SUM(Amount)
            FOR [NewName] in (' + @cols + ')
        ) p ';

EXECUTE(@query);

But the problem is I'd like to input the results into a temporary table #xxx and then later on be able to use this data via SELECT * FROM #xxx.

Do I need to CREATE #xxx using dynamic sql before running the above? If so can anyone point me in the direction of an example where this is done.

jdl · Accepted Answer · 2013-02-19 14:29:57Z

2

Frederic is almost right -

Dynamic SQL is executed in a separate scope than the calling batch. Any temporary objects (tables, variables) declared within the dynamic SQL batch are only available within the dynamic SQL batch. You will be able to SELECT INTO a global temporary table - since those are connection specific and will persist across the dynamic SQL batch.

sp_executesql

answered Feb 19, 2013 at 14:29

jdl

1,1048 silver badges12 bronze badges

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

1 Comment

Frederic Over a year ago

yeah I did a little mistake... I did change my answer for using global temporary table... ty...

Frederic · Accepted Answer · 2013-02-19 14:44:02Z

2

try change including into clause

SET @query =    
'SELECT [Measure],' + @cols + ' 
 into ##xxx
FROM  
    (
        SELECT  [NewName], [Measure], [Amount]
        FROM    #UnpivottedData x
    ) x
    PIVOT 
    (
        SUM(Amount)
        FOR [NewName] in (' + @cols + ')
    ) p ';

this will create the temp table for the results...

if you realy want to create before the select..

you would need to gather the types of the columns to build the create table statment..

edited Feb 19, 2013 at 14:44

answered Feb 19, 2013 at 14:23

Frederic

1,0286 silver badges11 bronze badges

4 Comments

Lamak Over a year ago

This way the #xxx temporary table won't be available after executing the dynamic sql, thus defeating the purpose of what op wants

Frederic Over a year ago

yeah.. its true.. sorry, @Lamak.. hum.. would have to use a ##xxx to use this way

whytheq Over a year ago

@Frederic - nice hack of the ## - I'd not seen it used like this before

Frederic Over a year ago

@whytheq , keep on mind that global temporary tables are visible for all connections.. and will persist there while exists an connection that references this table.. the table will be shared if you have two process, or more, that use this temporary table.. you may need to give different names for the ##xxx if need to isolate the process in some way... I am not sure.. but I guess will raise a error saying that the table already exists if you run this command simultaneously in two connections giving the same name for the ##xxxx

Daniel · Accepted Answer · 2013-02-19 18:40:29Z

Seems that you are able to get the column names, so I'm assuming you can get to the column types. So you can do this (pesudo code)

CREATE TABLE #XXX (Measure <type>)
//
   generate scripts like 
SET @SQL = N'ALTER TABLE #XXX ADD '+ ColumnName+' '+ColumnTypeInfo
EXEC SP_ExecuteSQL @SQL
//

SET @query =    
    'INSERT INTO #XXX ([MEASURE], '+@cols+')
     SELECT [Measure],' + @cols + ' 
    FROM  
        (
            SELECT  [NewName], [Measure], [Amount]
            FROM    #UnpivottedData x
        ) x
        PIVOT 
        (
            SUM(Amount)
            FOR [NewName] in (' + @cols + ')
        ) p ';

EXECUTE(@query);

P.S.

I, myself, would choose EXEC sp_executeSQL @SQL over EXEC (@SQL), but in this case is just a matter of taste
I would simplify the code - you don't really need the STUFF part because you need your first comma in your concatenation, so there is no need to remove it. Also, you are forcing an XML column ( with the TYPE clause) only to convert it to NVARCHAR

Later edit

Disclaimer: this is not tested, as I didn't have your table structure and data

DECLARE @cols NVARCHAR(MAX),
        @query NVARCHAR(MAX), 
        @alter_query NVARCHAR(max);

SET @cols = 
        (
        SELECT   ',' + QUOTENAME(b."NewName")
        FROM    (
                    SELECT  myKey, 
                        win = SUM(win)
                    FROM    xxx.dbo.yyy
                    GROUP BY myKey
                    ) x
                    INNER JOIN #NameSwitch b ON
                        x.myKey = b.myKey
        ORDER BY x.win DESC
        FOR XML PATH('')
        ) -- this will render smth like ",col1,col2" 
SET @alter_query = 
        (
        SELECT   ';ALTER TABLE #XXX ADD ' + QUOTENAME(b."NewName") +' '+ QUOTENAME(b."ColType") +' NULL'
        FROM    (
                    SELECT  myKey, 
                        win = SUM(win)
                    FROM    xxx.dbo.yyy
                    GROUP BY myKey
                    ) x
                    INNER JOIN #NameSwitch b ON
                        x.myKey = b.myKey
        ORDER BY x.win DESC
        FOR XML PATH('')
        ) -- this will render smth like ";ALTER TABLE #XXX ADD col1 VARCHAR(MAX);ALTER ..." 

CREATE TABLE #XXX (Measure INT NULL)

EXEC sp_ExecuteSQL @alter_query

SET @query =    
    'INSERT INTO #XXX (Measure+'@Cols+') SELECT [Measure]' + @cols + ' 
    FROM  
        (
            SELECT  [NewName], [Measure], [Amount]
            FROM    #UnpivottedData x
        ) x
        PIVOT 
        (
            SUM(Amount)
            FOR [NewName] in (' + STUFF(@cols, 1, 1, '') + ') -- here you need the STUFF to remove the first comma
        ) p ';


EXEC sp_ExecuteSQL @Query

SELECT * from #XXX

+1 @Daniel thanks for the help and the extra tips; I think yours is probably more what I was looking for (although the use of ## tables in the other answer is pretty nifty). Are you saying I can just drop 'STUFF' and it will still run? Have you got time to simplify my SET @cols = ... script and place the simplified version in your answer?
thanks for all the help/advice - it all seems to work ok - I think I need to read up about this FOR XML PATH as it seems pretty powerful

Collectives™ on Stack Overflow

Using dynamic SQL to create a table

3 Answers 3

1 Comment

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related