SQL- Temporary table in SQL SERVER

Temporary table in SQL SERVER

Temporary table: We create temporary table for temporary storing of data as the name suggests. It is very similar to normal tables and one can perform all operations on temporary 
table.Temprory tables are created inside tempdb database.The lifetime or the scope of 
the temporary table is limited.There are two kinds of temporary table which are as 
follows:

Local temporary table: It is prefixed with # to the table name.It is visible  only to the one who has created it till the session  exists. Table gets automatically deleted when the user log out from the session. The user can also explicitly drop the temporary table by using DROP command.
   Following below query shown is an example to create local temporary  table.

Creating Local Temporary Table

CREATE TABLE #Temp
(
  ID INT,
  Name VARCHAR(30),
  Age INT

  )

Inserting values into Local Temporary Table

INSERT INTO #Temp VALUES(1,'Sunil',24)
INSERT INTO #Temp VALUES(2,'Shweta',22)
INSERT INTO #Temp VALUES(3,'Rohit',25)
INSERT INTO #Temp VALUES(4,'Sohan',21)
INSERT INTO #Temp VALUES(5,'Mohan',21)

Let us see the data in #temp

#Temp Table

Once we are done with local temp table , we can explicitly delete table by using DROP command.

DROP TABLE #temp

Global temporary table: It is prefixed with ## to the table name. It is visible to the multiple users once the connection or session has been created.Table gets automatically deleted when all the user referencing to that table logs out from the session.The user can also explicitly drop the temporary table by using DROP command.
      Following below query shown is an example to create global temporary  table.

CREATE TABLE ##Temp1
(
  ID INT,
  Name VARCHAR(30),
  Age INT

  )

Inserting values into Global Temporary Table

INSERT INTO ##Temp VALUES(1,'Sunil',24)
INSERT INTO ##Temp VALUES(2,'Shweta',22)
INSERT INTO ##Temp VALUES(3,'Rohit',25)
INSERT INTO ##Temp VALUES(4,'Sohan',21)

INSERT INTO ##Temp VALUES(5,'Mohan',21)

Let us see the data in ##temp

##Temp Table

Once we are done with global temp table , we can explicitly delete table by using DROP command.

DROP TABLE ##temp