Create temporary table with dynamic number of columns

Question

I'm trying to create a temporary table with a dynamic number of columns:

set @cmd = ' SELECT * into #temp3 from
            (
                select * from sometable
            ) x pivot
            (
                max(buildrate)
                for name in ('+ @columns +')
            ) as y '

execute(@cmd);

select * from #temp3 left join performed in an elegant way... 

and I need to use contents from that table in other processing.

Is there any reasonable way to do this?

Solution 1

One workaround is to use global temporary table:

SET @cmd = ' SELECT * INTO ##temp3 FROM
            (
                select * from sometable
            ) x pivot
            (
                max(buildrate)
                for name in ('+ @columns +')
            ) as y '

EXECUTE(@cmd);

SELECT *
INTO #temp3
FROM ##temp3;

DROP TABLE ##temp3;

SELECT *
FROM JOIN #temp3 
LEFT ...;

The normal local temporary table won't work, because Dynamic SQL creates new context. The table is in that context and will cease to exist when code is executed, so you cannot use it outside Dynamic-SQL.

Solution 2

I create this script for test a dynamic temporary table.

I have Based on post in https://celedonpartners.com/blog/sql-server-how-to-a-create-temp-table-with-dynamic-column-names/

Hope this could help.

-- create and populate table
DROP TABLE dbo.sometable
GO

CREATE TABLE dbo.sometable(
    daterate datetime,
    name varchar(100),
    buildrate decimal(10,3),        
)
GO

DECLARE @COUNT INT

SET @COUNT = 0

WHILE @COUNT < 1000
BEGIN

    IF (CAST(RAND() * 2 AS INT) % 2) = 1
    BEGIN
        INSERT INTO dbo.sometable(daterate, name, buildrate) values(DATEADD(DAY, RAND() * -180, GETDATE()), 'Jeff', RAND() * 25.0)    
        INSERT INTO dbo.sometable(daterate, name, buildrate) values(DATEADD(DAY, RAND() * -180, GETDATE()),'Andrew', RAND() * 25.0)
        INSERT INTO dbo.sometable(daterate, name, buildrate) values(DATEADD(DAY, RAND() * -180, GETDATE()),'Mary', RAND() * 25.0)
        INSERT INTO dbo.sometable(daterate, name, buildrate) values(DATEADD(DAY, RAND() * -180, GETDATE()),'Carl', RAND() * 25.0)
        INSERT INTO dbo.sometable(daterate, name, buildrate) values(DATEADD(DAY, RAND() * -180, GETDATE()),'Zack', RAND()  * 25.0)
    END
    ELSE 
    BEGIN
        INSERT INTO dbo.sometable(daterate, name, buildrate) values(DATEADD(DAY, RAND() * -180, GETDATE()),'Jack', RAND()  * 50.0)
        INSERT INTO dbo.sometable(daterate, name, buildrate) values(DATEADD(DAY, RAND() * -180, GETDATE()),'Mag', RAND()  * 50.0)
        INSERT INTO dbo.sometable(daterate, name, buildrate) values(DATEADD(DAY, RAND() * -180, GETDATE()),'Kim', RAND()  * 50.0)
        INSERT INTO dbo.sometable(daterate, name, buildrate) values(DATEADD(DAY, RAND() * -180, GETDATE()),'Suse', RAND()  * 50.0)
    END    
    SET @COUNT = @COUNT + 1
END    
GO


-- execute query with the created table
DECLARE @columns VARCHAR(MAX)
      , @columns_name varchar(max)
      , @sql varchar(max)

IF OBJECT_ID('tempdb..#temp3') != 0
    DROP TABLE #temp3

SELECT 
    @columns = COALESCE(@columns + ', ', '') + '[' + name + '] float'
   ,@columns_name = COALESCE(@columns_name + ', ', '') + '[' + name + ']'
FROM (SELECT DISTINCT NAME FROM sometable) VW
order by name


CREATE TABLE #temp3(   
   daterate varchar(10)       
)

EXEC('alter table #temp3 add ' + @columns)

SET @sql = 'insert into #temp3 
            select * from
            (
                select name, buildrate, right(convert(varchar(10), daterate, 103), 7) as daterate  from sometable
            ) x pivot
            (
                max(buildrate)
                for name in ('+ @columns_name +')
            ) as y '

EXEC(@sql)

SELECT * FROM #temp3 

Comments

[Ocaso Protal]

Obligatory link to sommarskog.se: sommarskog.se/dynamic_sql.html#unknowncolumns Read the whole article to get enlightend on dynamic SQL.

[Buda Florin]

Almoust closed the page when I reached this " If you are working with a relational database, and you don't know the structure of your data until run-time, then there is something fundamentally wrong."

[Muflix]

what if this procedure will be called by multiple users simultaneously ? Can there be a situation where we will be inserting to the #temp3 when ##temp3 will be deleted ?

[Buda Florin]

wouldn't be more simple to make something like '##temp_' + MD5(getdate()) ? ;)

[Buda Florin]

just for debate reasons: you could use a "real" table instead of #temp3 and never delete the real table. And to handle the multi user situation just add an username column in that table and perhaps delete previous data for any new requests. I just hope I won't have a colleague that will have that approach.

[Buda Florin]

what happens if two peoples uses the same script in the same moment?

[Sid Sousa]

the temporary table used in this case is by session, no one will affect each other. I think so... wow!!

[Doctor Rudolf]

@Muffix: Yes, that is why I am looking for some other type of solution...