Temporary Table in SQL Server

There are two types of temp tables i.e. Local temp table and Global temp table. Local temp table are available to the current user of the instance and the global temp table is available for any user after created.

Local temp Table:
Temp Table is just like to permanent table. It is stored in tempdb. The difference of temp table is that Temp table deleted when SQL server instance is closed. (#) hash sign is used before the name of the table. This is called local temp table. Local temp table is available only for the current connection of the user. The syntax of temp table is given below.


/* Create Temp table */
CREATE TABLE #tempTable
(
ID INT,
FirstName VARCHAR(40)
)

/* Insert data into temp table */
Insert into #tempTable (ID, FirstName)
VALUES (1, 'Ather Abbas')

/* Select temp table data that we just inserted */
Select * from #tempTable

/* Drop Temp Table  */
DROP TABLE #tempTable


Global Temp Table:
As mentioned above global temp table are available for any user of database. The syntax are same as local temp table just use ## double hash sing before the name of the table. The syntax are given below.
/* Create Temp table */
CREATE TABLE ##tempTable
(
ID                INT,
FirstName   VARCHAR(40)
)

/* Insert data into temp table */
Insert into ##tempTable (ID, FirstName)
VALUES (1, ‘Ather Abbas’)

/* Select temp table data that we just inserted */
Select * from ##tempTable

/* Drop Temp Table  */
DROP TABLE ##tempTable