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:
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.
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))
SELECT tempfield1 as id, tempfield2 as NameINTO #TempTable1 FROM ActualTable1
--replace # to ##CREATE TABLE ##TempTable1( tempfield1 INT, tempfield2 VARCHAR(50))
SELECT tempfield1 as id, tempfield2 as NameINTO ##TempTable1 FROM ActualTable1
use below stored procedure for checking local #TempTable1 table:
IF OBJECT_ID('tempdb..#TempTable1') IS NOT NULLBEGIN PRINT '#TempTable1 exists'ENDELSEBEGIN PRINT '#TempTable1 does not exist'END
IF OBJECT_ID('tempdb..##TempTable1') IS NOT NULLBEGIN PRINT '##TempTable1 exists'ENDELSEBEGIN PRINT '##TempTable1 does not exist'END
very useful content..
thank you for providing such a nice article..
Copyright © 2011-2017 DotNetSquare. All Rights Reserved. Designed & Developed By : Manoranjan Sahoo