Creating Temporary Table using T-SQL

Before creating the Local temporary table, let see the tempdb  database, initially there will be no custom created temporary table as in the image.

Now we are going to create the Temporary table

CREATE TABLE #MyTempTable_Local

( 

  id int,

  name varchar(20),

  city varchar(10),

  zone varchar (10)   

) 

Now if we see the tempdb New temporary table is created

 

Let see the table by select statement

select * from tempdb..#MyTempTable_Local 

 

 

 

 

Am going to insert new data

insert into #MyTempTable_Local values(1,’Name1′,’Chennai’,’TN’)

insert into #MyTempTable_Local values(2,’Name2′,’Mumbai’,’MP’)

insert into #MyTempTable_Local values(3,’Name3′,’Noida’,’UP’)

 

select * from tempdb..#MyTempTable_Local 

when you try to run in different Query Editor, you got the error message that tables not found.

 

 

 

Local Temporary table (#) only available in Query Editor where you create the Temporary table.

Now we will create Global Temporary table

CREATE TABLE ##MyTempTable_Global
(
  id int,
  name varchar(20),
  city varchar(10),
  zone varchar (10)
)

Now we will insert new data to ##MyTempTable_Global

insert into ##MyTempTable_Global  values(1,’Name1′,’Chennai’,’TN’)

insert into ##MyTempTable_Global  values(2,’Name2′,’Mumbai’,’MP’)

insert into ##MyTempTable_Global  values(3,’Name3′,’Noida’,’UP’)

Now try to open the new Query editor and write the select statement for  ##MyTempTable_Global  

select * from tempdb..##MyTempTable_Global  

You can able to fetch the value from the  Global  Temporary Table(##) ,because it is available in all session, but Local Temporary Table (#)  is available only in current session

About Temporary Table in SQL Server

Temporary table is one of the concept in SQL Server which help the developer in a great way. This tables can be created at runtime and can do the all kind of operation that one normal table can do.

Temporary table is just like normal table, we can create it at run time and have same kind of operation as in normal table.

Types of Temporary Table

1) Local (#,@)
2) Global (##)

1)      Local Temporary Table (#,@)

When temporary table name start with ‘#’ sing, then  temp tables are only available for the current connection. This table are destroyed automatically when the user disconnects  from instances. 

CREATE TABLE #MyTable 
( 
    id INT, 
    name VARCHAR(20) 
)

2)      Global(##)

When temporary table name start with ‘##’ sing, then  temp tables are available permanently to all users. This table are destroyed when all user connections are closed, else user have to destroyed manually.

CREATE TABLE ## MyTable 
( 
    id INT, 
    name VARCHAR(20) 
)

Normally Temporary is available in tempdb