Create temp table with variable on Microsoft SQL Server

Question

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!

There is a mapping table that contains source file names and destination tables for bulkinsert. Before bulkinsert, some data clean up in a temp table is necessary. So I am just trying to loop through this mapping list and set @TableName and create a temp table one by one. — E.K.
– E.K., Commented Jan 18, 2017 at 21:16

marc_s · Accepted Answer · 2017-01-18 21:48:23Z

2

DECLARE @TableName1 VARCHAR(50)

SET @TableName1 = 'dbo.encounter'

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

SELECT * FROM ##temp

edited Jan 18, 2017 at 21:48

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jan 18, 2017 at 21:29

Sudesh Yadav

323 bronze badges

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

Comments

DVT · Accepted Answer · 2017-01-18 21:27:02Z

1

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.

answered Jan 18, 2017 at 21:27

DVT

3,1274 gold badges16 silver badges25 bronze badges

Comments

Yashveer Singh · Accepted Answer · 2017-01-18 21:00:59Z

0

  DECLARE @sql NVARCHAR(MAX);

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

EXEC sp_executesql @sql

answered Jan 18, 2017 at 21:00

Yashveer Singh

2,0072 gold badges18 silver badges24 bronze badges

3 Comments

E.K. Over a year ago

This gives the same error Invalid object name '#tmp' Would you advice how can i use a variable @TableName to perform the task?

DVT Over a year ago

There is a typo in the string. You cannot see the table #tmpA when you SELECT INTO #temp.

RBT Over a year ago

May I request you to please add some more context around your answer. Code-only answers are difficult to understand. It will help the asker and future readers both if you can add more information in your post.

Collectives™ on Stack Overflow

Create temp table with variable on Microsoft SQL Server

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related