Temporary Tables in SQL Server
Sometimes we need to manipulate huge amount of data with store procedure. At this time we need to save data into temporary table and delete those after doing manipulation. Instead of creating simple Table as temporary table we can use Temporary Table of SQL Server. In this article I am going to explain some basic information regarding Temporary Table.
Introduction
Temporary table is very useful tool in SQL Server for developer. It provided short term use of data. These tables can be created at runtime and can do the all kinds of operations that one normal table can do. But, based on the table types, the scope is limited. These tables are created inside tempdb database. There are two types of temporary table in SQL Server:
1. Local
2. Global
Local : Local temporary tables are only available to the current connection to the database for the current user. These are dropped automatically when the connection is closed.
Global : Global temporary tables are available to all connections that created and exist to that database. These are dropped when the last connection using it is closed.
Both types of temporary tables are created in the system database tempdb.How to create Temporary Tables?
Temporary tables can be created like any table in SQL Server with a CREATE TABLE or SELECT..INTO statement.
To make the table as local temporary table, attach (#) as prefix with the name of the table.
To make the table as global temporary table, attach (##) as prefix with the name of the table.
Create Local Temporary Table using Create Table statement.
Code :
CREATE TABLE #TempTable1
(
tempfield1 INT,
tempfield2 VARCHAR(50)
)
Create Local Temporary Table using SELECT ... INTO statement.
Code :
SELECT
tempfield1 as id,
tempfield2 as Name
INTO #TempTable1 FROM ActualTable1
Above sample codes will create a local temporary table named #TempTable1 with two fields with name tempfield1 and tempfield2.
Create Global Temporary Table using Create Table statement.
Code :
--replace # to ##
CREATE TABLE ##TempTable1
(
tempfield1 INT,
tempfield2 VARCHAR(50)
)
Create Global Temporary Table using SELECT ... INTO statement.
Code :
SELECT
tempfield1 as id,
tempfield2 as Name
INTO ##TempTable1 FROM ActualTable1How to check if Temporary table exist?
use below stored procedure for checking local temp table:
IF OBJECT_ID('tempdb..#TempTable1') IS NOT NULL
BEGIN
PRINT '#TempTable1 exists'
END
ELSE
BEGIN
PRINT '#TempTable1 does not exist'
END
use below code for checking global TempTable1 table :
IF OBJECT_ID('tempdb..##TempTable1') IS NOT NULL
BEGIN
PRINT '##TempTable1 exists'
END
ELSE
BEGIN
PRINT '##TempTable1 does not exist'
END
Reference: http://dotnetsquare.com/resources/50-temporary-tables-in-sql-server
Follow
Hai Manoranjan,
Good article regrding the temp tables. But one thing i feel is missing here that why we go for tem tables? is there ny specific reason to go with temp table? i think if you would have explains that too would have been better than this. Also what is the scope of these tables? How is the accessibility options for these tables? whether any user can access these tables data from some other tables?
if possible then include those points also in you article..