CODEWITHSUNDEEP
sql-server
SQLiz
SQLiz

Reputation: 143

Issue SELECT with temporary table

I am able to DROP and CREATE a temporary table but when I do a select, it doesn't recognize the object. I know it's there but how can I access it? FYI, I have multiple databases in SQL Server (2008). I tried the below but it doesn't work.

SELECT * 
 FROM tempdb..#TBL_IMPORT

Usually to access tables I have to type this: dbname.dbo.tablename Any clue? Thank you.

Upvotes: 1

Views: 481

Answers (1)

Random_User
Random_User

Reputation: 363

With the amount of information given, the answer is in the comments.

If you would like to query a temp table from a second session, you'll need to create the temp table as a global temp table.

select *
  into ##MyGlobalTable
  from SourceTable

If you're using SSMS you will want to use the same window you create the temp table in if not using a global temp table. If you're using a secondary application you'll want to validate you're using the same SPID.

Other approaches you may be interested in would include CTEs (common table expressions) and variable tables. Google will have a wide assortment of assistance, or you could update your question here.

Upvotes: 1

Related Questions