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

Question

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

a temporary table is only visible for the current connection. — McNets
– McNets, Commented Nov 5, 2016 at 19:50
would the EXEC sp_executesql cause a new context so the previous #Temp table would no longer be visible? — Sql Surfer
– Sql Surfer, Commented Nov 6, 2016 at 0:20

Martin Smith · Accepted Answer · 2016-11-05 19:55:41Z

1

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

answered Nov 5, 2016 at 19:55

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

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

4 Comments

Nas_Gon Over a year ago

Martin Smith, I have tried both approaches above. approach 1. ##GlobalTemp does not get created.

Nas_Gon Over a year ago

approach 2. #T only has original column in it with no values in table.

Martin Smith Over a year ago

@Nas_Gon looks like you probably integrated it incorrectly then. It relies on @columns being populated as in your example code. as here rextester.com/KCM23794

Nas_Gon Over a year ago

Martin Smith, Thanks for the results as shown in link above @rextester.com It works great. Best Regards. Nas_Gon

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related