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?
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.
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
Based on @Ocaso Protal comment, i created this
-- create global temporary table with timestamp to prevent multiple user collision
DECLARE @timestamp NVARCHAR(255) = REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(23), GETDATE(), 121),'-',''),' ',''),':',''),'.','')
DECLARE @temp NVARCHAR(255) = '##temp_'+@timestamp
SET @query = 'CREATE TABLE ' + @temp + ' (RowName NVARCHAR(255), ' + @outputQueryColumns + ')'
EXECUTE sp_executesql @query
-- working with temporary table whether in procedure or in dynamic sql
-- delete global temporary table
SET @query = 'DROP TABLE ' + @temp
EXECUTE sp_executesql @query
