Working Temporary Tables in SQL Server using T-SQL using SQL Server Management Studio


Today our topic of discussion is working with temporary table in SQL Server using SQL Queries with SQL Server Management Studio.

Now a days, writing a query is not a complex as it start but working with some large data is not as easy as it says. 

Thus working with some large data or working with physical tables can slow down your performance of Data manipulation in your database. Thus we use Temporary tables to work with data which we have already stored with a Database Table in SQL Server.

There are 03 basic types of Temp tables 

  1. Declaring a Temp table with @ symbol
  2. Declaring or creating a temp table with single # symbol
  3. Declaring or creating a temp table with double # symbol
Let's discuss each an every one with some example. Each and every table has its unique syntax and structure and use of.


Table Declare with @ symbol can be declared with below syntax, In our one of the working database the Sample Database we have a table with MembersList


Now I will be storing this Information in a Temp table declare with a Structure in SQL Server Management Studio.


The temp table is created in the Memory and store Information that we have store. 


when we talk about creating a temp table with single # symbol, then it comes in mind to work with some larger data. The Scope of this type of table is to the Current query session which can't be accessible to other query window. These temp tables are created in Master default database and they remain until the drop or the SQL Server database engine services are reset.

The syntax we follow to store information in 02 ways.

First we can define a Temp table Structure and insert data in it and 2ndly we can just insert the defined physical table data with the defied table structure.


  if we try to select the #TempTable records in a new Query it show following Error

The second way we can use temp table with single # 




The basic difference in single # and double ## temp tables in SQL Server Management Studio is 

  • The table can be accessed in other Query session 
  • This table is also created in master database
  • This table exists until it is dropped out
The syntax of creating a table with Double ## is as 


Thus we can select the data stored in another query also with a new query session.