A temporary table is a database object that is temporarily stored and managed by the database system. Temporary tables can be local or global. Local temporary tables have physical representation—that is, they are stored in the tempdb system database. They are specified with the prefix # (for example, #table_name).
A local temporary table is owned by the session that created it and is visible only to that session. Such a table is thus automatically dropped when the creating session terminates. (If you define a local temporary table inside a stored procedure, it will be destroyed when the corresponding procedure terminates.)
Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the database server. In contrast to local temporary tables, global ones are specified with the prefix ##.
Examples 6.55 and 6.56 show how the temporary table project_temp can be created using two different Transact-SQL statements.
Example 6.55
USE sample;
CREATE TABLE #project_temp
(project_no CHAR(4) NOT NULL,
project_name CHAR(25) NOT NULL);
Example 6.56
USE sample;
SELECT project_no, project_name
INTO #project_temp1
FROM project;
Examples 6.55 and 6.56 are similar. They use two different Transact-SQL statements to create the local temporary table, #project_temp and #project_temp1, respectively. However, Example 6.55 leaves it empty, while Example 6.56 populates the temporary table with the data from the project table.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.