Temporary Table Usage in SQL2005

Blog describes what is temporary table and what is its scope with a simple example

As the name suggests the table is temporary and it will get deleted after the usage. SQL developer uses temp table and store the some computation result in it temporarily and query them later. Say for example, if your computation involves data from 6 or 7 tables, apart from other techniques Temp table also one nice techniques to make calculations and store it table temporarily for later use in that session.

 

To create the temp table, use the following syntax Example:

Select <Column_list> into #<Temp_Table_name> From <OriginalTableName>

 

In the above syntax:

Column_List: The list of column from the main table. You can use comma separated column names or * for all columns

#<Temp_Table_name>: Name of the temporary table. Ex: #MyTemp

 <OriginalTableName>: Name of the original table from which the data is retrieved

 

Below is the example screen shot for the usage of the temp table. The NorthWnd database is used in two different query window.

 

Query Session7

 

 

 

Query Session5

 

 

We have two queries window, and in one query window we are filtering the employees from Seattle and in other window we are filtering the employees from London. The information retrieved is stored in the #EmpTemp table. The table with the # sign is known as temporary table. In our example it is created for the session for query window shown in red box as well as green box.

 

The temp table will get deleted automatically when the Query window is closed. That means the scope of the temporary table is connection-based sessions. And note that each query window maintains separate connection to the database. This is why you get an error when you try to execute the query shown above again in the same query window. After retrieving the data, SQL tries to create the temporary table and the creation fails, as it already exists because of the previous execution in the same connection to the query window.

 

Now If you look, I used same #EmpTemp in the second query window. Does it effects the content of the #EmpTemp create in the query window marked n red? No. Because, the temp table scope is session based and SQL see both the temporary table as two different instances and the same name does not matter.