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, Mar 23 '15 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, Mar 23 '15 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, Mar 23 '15 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, Mar 23 '15 at 13:57

score 5 · Accepted Answer · answered Mar 23 '15 at 13:56

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 '15 at 13:56

JustAPup

1 Answers1