TSQL - Temptables using Dynamic SQL

Interesting learning on Temptables using Dynamic SQL. When I use temp tables in Dymanic SQL my table creation failed. I have simplified this into example provided below.

--Example Code

IF OBJECT_ID('tempdb..#T2') IS NOT NULL
    DROP TABLE #T2;
GO   
IF OBJECT_ID('dbo.T1') IS NOT NULL
    DROP TABLE dbo.T1;
GO
-- Test Tables
CREATE TABLE dbo.T1 (
a [int] NOT NULL,
b [int] NOT NULL)

--Populate Data
INSERT INTO T1 (a,b)
VALUES(10, 20),(10,30),(20,30)

DECLARE @sql nvarchar(max)

SET @sql = 'SELECT T1.a AS Col1, SUM(T1.b) AS Col2 INTO #T2 FROM T1 GROUP BY T1.a'
PRINT @sql 

EXEC sp_executesql @sql

SELECT * FROM #T2

When you execute this example you would receive error

--Error

Line 2: Msg 3701, Level 11, State 5:
Cannot drop the table '#T2', because it does not exist or you do NOT have permission.

There are two possible solutions for this. One is using global temptables (Use ## to make it global temp tables), second solution is create the table before calling insert records. I found similar examples while searching for this error.

Solution #1 - USE GLOBAL Temp Tables ##

--Example Code
IF OBJECT_ID('tempdb..##T2') IS NOT NULL
    DROP TABLE ##T2;
GO   
IF OBJECT_ID('dbo.T1') IS NOT NULL
    DROP TABLE dbo.T1;
GO
-- Test Tables
CREATE TABLE dbo.T1 (
a [int] NOT NULL,
b [int] NOT NULL)

--Populate Data
INSERT INTO T1 (a,b)
VALUES(10, 20),(10,30),(20,30)

DECLARE @sql nvarchar(max)
SET @sql = 'SELECT T1.a AS Col1, SUM(T1.b) AS Col2 INTO ##T2 FROM T1 GROUP BY T1.a'
PRINT @sql 
EXEC sp_executesql @sql
SELECT * FROM ##T2

Solution #2 - Based on post . CREATE the TABLE before calling the INSERT query


--Example Code
IF OBJECT_ID('tempdb..#T2') IS NOT NULL
    DROP TABLE #T2;
GO   
IF OBJECT_ID('tempdb..#T3') IS NOT NULL
    DROP TABLE #T3;
GO   
IF OBJECT_ID('dbo.T1') IS NOT NULL
    DROP TABLE dbo.T1;
GO

-- Test Tables

CREATE TABLE dbo.T1 (
a [int] NOT NULL,
b [int] NOT NULL)

CREATE TABLE #T2 (
a [int] NOT NULL,
b [int] NOT NULL)

CREATE TABLE #T3 (

a [int] NOT NULL,
b [int] NOT NULL)

--Populate Data

INSERT INTO T1 (a,b)
VALUES(10, 20),(10,30),(20,30)

DECLARE @sql1 nvarchar(max)

SET @sql1 = 'SELECT T1.a AS Col1, SUM(T1.b) AS Col2 INTO #T3 FROM T1 GROUP BY T1.a'

PRINT @sql1

DECLARE @sql nvarchar(max)

SET @sql = 'INSERT INTO #T2(a,b) SELECT T1.a AS Col1, SUM(T1.b) AS Col2 FROM T1 GROUP BY T1.a'

PRINT @sql

EXEC sp_executesql @sql1

EXEC sp_executesql @sql

SELECT * FROM #T2

SELECT * FROM #T3

Table#3 had 0 records, Table #2 contained correct data set.

Happy Learning!!!