Global and local scopes of SQL temporary tables

Temporary table
SQL Server supports temporary tables. A temporary table is a table whose names start with a pound sign. If the temporary table is not removed when the user disconnects, SQL Server automatically removes the temporary table. Temporary tables are not stored in the current database, but stored in the system database tempdb.

There are two types of temporary tables:

Local temporary table
Names of tables starting with. You can view these tables only when you create a connection to a local temporary table.

Global temporary table
The names of the tables starting with two pound signs. The global temporary table is displayed on all connections. If these tables are not explicitly removed before the connection to create a global temporary table is disconnected, the tables are removed as long as all other tasks stop referencing them. When the connection to the global temporary table is disconnected, the new tasks cannot reference them any more. After the current statement is executed, the association between the task and the table is removed. Therefore, the global temporary table is removed as long as the connection to the global temporary table is disconnected.
-- Apply a local temporary table
Declare @ SQL varchar (100)
Set @ SQL = 'select' 123 ''as a, ''abasdf'' as B into # t'
Exec (@ SQL)
Select * from # t
Go
/* The data in the temporary table cannot be correctly displayed.
Tip:
Server: Message 208, level 16, status 1, Row 5
The object name '# T' is invalid.
*/

-- Change to a global temporary table
Declare @ SQL varchar (100)
Set @ SQL = 'select' 123 ''as a, ''abasdf'' as B into # t'
Exec (@ SQL)
Select * from # t
Drop table # t
Go
/* The data in the temporary table can be correctly displayed.
Cause: exec (@ SQL) is equivalent to re-establishing a database conversation. Therefore, for a local temporary table, use exec (@ SQL) to create a temporary table,
Access is not allowed outside exec, but it is OK to use the global temporary table.
*/

SQL code

   exec   ('select   top   10   *   into   ##temp   from   syhouse'); exec   ('select   *   from   ##temp');