SQL SELECT results INTO temp table using query string

Question

I am trying to write some dynamic SQL queries that select results into a temp table with a query string. It looks like follows:

DECLARE @SQL Varchar(4000)

SET @SQL = 'SELECT * INTO #tmp_tab FROM dbo.sometable'

EXEC(@SQL)

It doesn't give any error to run the code, but when I want to select from #tmp_tab, it says the table doesn't exist.

So I am wondering if there is any special syntax for it, or dynamic SQL doesn't support such operation?

Many thanks.

your temp table exists in the scope of the dynamic SQL, not outside of it. — Lamak
– Lamak, Commented Mar 23, 2015 at 13:54
@Lamak Hi, thanks for your reply. Then how can I visit it from outside? I thought it is existing in tempDB, isn't it? — ChangeMyName
– ChangeMyName, Commented Mar 23, 2015 at 13:55
No, you can't, the same way that another user can't see a temp table that you create. Unless you create the temp table outside your dynamic SQL and then insert data using dynamic SQL, you won't be able to use it — Lamak
– Lamak, Commented Mar 23, 2015 at 13:57
Please tell us what concrete database system this is for - many things are vendor-specific. Are you using MySQL? Postgres? SQL Server? Oracle? IBM DB2? Something else entirely? Please update your tags to show what database system (and which version of it!) you're using - thanks! — marc_s
– marc_s, Commented Mar 23, 2015 at 13:57

JustAPup · Accepted Answer · 2015-03-23 13:56:27Z

5

Maybe it has something to do with access. If you create a global temp table, it will work.

DECLARE @SQL Varchar(4000)
SET @SQL = 'SELECT * INTO ##tmp_tab FROM dbo.batch'
EXEC(@SQL)

SELECT * FROM ##tmp_tab

answered Mar 23, 2015 at 13:56

JustAPup

1,79013 silver badges19 bronze badges

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

1 Answer 1