CODEWITHSUNDEEP
sql-servervariablestemp-tables
E.K.
E.K.

Reputation: 4349

Create temp table with variable on Microsoft SQL Server

I am trying to create a temp table on Microsoft SQL Server 2014 using a variable. 

DECLARE @TableName VARCHAR(50)
SET @TableName = 'MyShema.MyTable'

EXEC('SELECT * INTO #temp FROM ' + @TableName)
SELECT * FROM #temp

However, the last line, SELECT * FROM #temp gives Invalid object name '#temp. Can anyone please tell me how I can fix it? Thank you!

Upvotes: 1

Views: 1897

Answers (3)

Sudesh Yadav
Sudesh Yadav

Reputation: 32

DECLARE @TableName1 VARCHAR(50)

SET @TableName1 = 'dbo.encounter'

EXEC('SELECT * INTO ##temp FROM ' + @TableName1)

SELECT * FROM ##temp

Upvotes: 2

DVT
DVT

Reputation: 3127

Check out this link.

http://www.sommarskog.se/dynamic_sql.html#Dyn_table

The temp table created in a dynamic SQL statement is dropped at the exit of that statement. That is why you got the error.

Upvotes: 1

Yashveer Singh
Yashveer Singh

Reputation: 1977

  DECLARE @sql NVARCHAR(MAX);

  SET @sql = N'SELECT * INTO #temp FROM from MyShema.MyTable ; SELECT * FROM #tmpA';

EXEC sp_executesql @sql

Upvotes: 0

Related Questions