The syntax of a temporary table is like a physical table in Microsoft SQL Server with the exception of the use of sign (#).
There are two types of temporary tables: local and global.
Local temporary tables are visible only to user who created the temp table during the same connection to an instance of SQL Server.
Local temporary tables and are deleted after the user disconnects from the instance of SQL Server.
Global temporary tables are visible to all users and to all connections.
Global temporary tables are deleted when all users that are referencing the table disconnect from the instance of SQL Server.
Syntax for local temp tables
CREATE TABLE #local_table
(
column_name_1 data_type,
column_name_2 data_type,
....
column_name_n data_type
);
Syntax for global temp tables
CREATE TABLE ##global_table
(
column_name_1 data_type,
column_name_2 data_type,
....
column_name_n data_type
);
Example
CREATE TABLE #TEST_LOCAL (
ID INT ,
NAME VARCHAR (250) ,
HIREDATE DATE ,
);
CREATE TABLE ##TEST_GLOBAL (
ID INT ,
NAME VARCHAR (250) ,
HIREDATE DATE ,
);