SELECT QUERY OF TEMP TABLE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

SELECT QUERY OF TEMP TABLE

Author

Topic

doco
Yak Posting Veteran

77 Posts

Posted - 2009-02-22 : 11:50:34

[ relatively new to SQL ]

I have created a temp table by the following means



SELECT [fields]
INTO #temp_table
FROM [table]
WHERE [criteria]
-- test
SELECT * FROM #temp_table

The temp table in fact exists under tempdb, etc. and the test select query returns the expected dataset.

However, if a new query page is instanced with a select query run or a select query is run from any outside query tool, table doesn't exist errors are returned. If I fully qualify the FROM statement to include tempdb.dbo.#temp_table; tempdb..#temp_table; dbo.#temp_table all return 'ignored ' { tempdb, dbo } then table does not exist.

I got two returns when searching this forum for 'query temp table' neither addressed the issue. So, how does one create a temp table then access it by query afterwards?

TIA

Education is what you have after you've forgotten everything you learned in school

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-22 : 12:52:45

Because local temp table are only limited to current session.

doco
Yak Posting Veteran

77 Posts

Posted - 2009-02-22 : 15:27:30

quote:
Originally posted by sodeep

Because local temp table are only limited to current session.

Not much sense in creating them if they cannot be used later. So, what you are saying is a query against the temp table can only be performed immediately after their production and in the same script?

Education is what you have after you've forgotten everything you learned in school

SQLforGirls
Starting Member

48 Posts

Posted - 2009-02-22 : 17:58:06

Yes, temp tables are similar to internal variables. They are intended for use by the current session, script, stored proc, etc. One of the reasons/benefits of this is that multiple instances of the same query page can be running similar code, and since each temp table is limited to its session, the different session's temp tables will not conflict.

If you need to create a table in one session, and access it in another session, you need to first address the potential problems that concurrent instances of the same query page might pose.

Aside from that (major) issue, you simply need to create either a permanent table, or a Global temp table (## instead of #). A global temp table is accessible by other sessions outside the session that created it, but it will be automatically dropped when either the session that created it, or the last active session that accessed it ends. Global temp tables are usually only used in very narrow circumstances. They do not allow you to create multiple instances of the same table name inside multiple sessions. They drop themselves, and they (generally) reside in memory rather than on disk. I believe those are really the only differences between a global temp and a permanent table.

I don't know if this provides a solution to your problem, but hopefully it adds some information that helps you decide what you can do.

doco
Yak Posting Veteran

77 Posts

Posted - 2009-02-24 : 09:25:03

Thank you.

The [real] solution is one I have been avoiding and am going to have to implement - full read/write permission. I own the data so guess I am going to have to pull rank on my DBA.

Thanks again.

Education is what you have after you've forgotten everything you learned in school

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources