2

I create a temp table with the following scripts

SELECT *
INTO #TEMP
FROM [mySchema].[Mytable]

The temp table location is at System Databases>Tempdp>temporary table>dbo.#Temp

How can I query the temp table in SQL Server New Query Windows from SQL Server Management Studio (SSMS) (V18.9.2)

Based on Zaynul Abadin Tuhin Answer: We can query the global temp table by creating ##Temp

SELECT *

INTO ##TEMP

FROM [mySchema].[Mytable]

Anson
  • 243
  • 1
  • 5
  • 20

2 Answers2

3

As already noted, a temporary table created with the single hash/pound # prefix is visible only to the connection that created it.

However, there's nothing stopping you from creating a normal user table in tempDB and benefitting from the various optimisations that exist for TempDB.

Of course any table you create you'll need to manage/remove yourself, it won't be automatically removed like a #table would be, but as it's a normal table any connection can see it.

Stu
  • 30,392
  • 6
  • 14
  • 33
1

Temporary table is just visible to session that created the temporary table.msdn reference

Temporary Tables
You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions.

New Query Window is a new session, so it cannot refer to the temporary table created in another session.

Venkataraman R
  • 12,181
  • 2
  • 31
  • 58