Home > SQL > Creating Temporary Table using T-SQL

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

Advertisements
Categories: SQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: