CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Nas_Gon
Nas_Gon

Reputation: 21

Dynamic SQL SQL Server 2012 Get Result into a #temp table

I am using SQL Server 2012. I will like to get final result from @sql into a temp table.Here is my code so far. Thanks for your help.

IF OBJECT_ID ('tempdb.dbo.#MY_DT_CTE') IS NOT NULL DROP TABLE #MY_DT_CTE
CREATE TABLE #MY_DT_CTE
([ROWID] INT NOT NULL IDENTITY (1,1)
,[YYYYMM] INT
)
; WITH MY_DT_CTE AS
(
SELECT CONVERT(INT,CONVERT(VARCHAR(6),EOMONTH(GETDATE(),-1),112)) AS [YYYYMM]
UNION
SELECT CONVERT(INT,CONVERT(VARCHAR(6),EOMONTH(GETDATE(),-2),112)) AS [YYYYMM]
UNION
SELECT CONVERT(INT,CONVERT(VARCHAR(6),EOMONTH(GETDATE(),-3),112)) AS [YYYYMM]
UNION
SELECT CONVERT(INT,CONVERT(VARCHAR(6),EOMONTH(GETDATE(),-4),112)) AS [YYYYMM]
UNION
SELECT CONVERT(INT,CONVERT(VARCHAR(6),EOMONTH(GETDATE(),-5),112)) AS [YYYYMM]
UNION
SELECT CONVERT(INT,CONVERT(VARCHAR(6),EOMONTH(GETDATE(),-6),112)) AS [YYYYMM]
)
INSERT INTO #MY_DT_CTE
SELECT [YYYYMM] FROM MY_DT_CTE
ORDER BY [YYYYMM] DESC;
-- SELECT * FROM #MY_DT_CTE

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(YYYYMM)
FROM (SELECT p.YYYYMM FROM #MY_DT_CTE AS p
GROUP BY p.YYYYMM) AS x;
SET @sql = N'
SELECT ' + STUFF(@columns, 1, 2, '') + '
FROM
(
SELECT p.YYYYMM FROM #MY_DT_CTE AS p
) AS j
PIVOT
(
COUNT(YYYYMM) FOR YYYYMM IN ('
+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1,
'')
+ ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;

Need to bring this result @sql into a temp table. 201605 201606 201607 201608 201609 201610

Upvotes: 2

Views: 77

Answers (1)

Martin Smith
Martin Smith

Reputation: 453897

An easy way would be to use a global temporary table. This can be selected into in the dynamic SQL scope to automatically have the desired schema and still be available after that exits.

SET @sql = N'
SELECT ' + STUFF(@columns, 1, 2, '') + '
INTO ##GlobalTemp
FROM
(
SELECT p.YYYYMM FROM #MY_DT_CTE AS p
) AS j
PIVOT
(
COUNT(YYYYMM) FOR YYYYMM IN (' + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '') + ')
) AS p;';

EXEC sp_executesql @sql;

SELECT *
FROM   ##GlobalTemp

However this can cause issues with naming clashes if the code is ever executed concurrently.

It is possible to use a local temp table but the code is more involved as it involves creating a temp table at the upper scope and then using dynamic SQL to alter it to the dynamically determined schema before inserting into it.

CREATE TABLE #T
  (
     Dummy INT
  );

SET @sql = 'ALTER TABLE #T ADD   Dummy2 INT' + REPLACE(REPLACE(@columns, 'p.', ''), ']', '] int') + ';
            ALTER TABLE #T DROP COLUMN Dummy, Dummy2;'

EXEC (@sql);

SET @sql = N'
INSERT INTO #T
SELECT ' + STUFF(@columns, 1, 2, '') + '
FROM
(
SELECT p.YYYYMM FROM #MY_DT_CTE AS p
) AS j
PIVOT
(
COUNT(YYYYMM) FOR YYYYMM IN (' + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '') + ')
) AS p;';

EXEC sp_executesql @sql;

SELECT *
FROM   #T

Upvotes: 1

Related Questions