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