Temporary Tables in SQL SERVER 2012

SQL SERVER Temporary Tables.

                       SQL Server Temporary tables are used to store table information in Temp Database. These temporary tables are shot lived i.e up to the session time,   once session is closed these tables are automatically dropped and data will be deleted.

There are 2 types of temorary tables

1.                Local Temporary tables
2.                Global Temporary tables.

Local Temporary tables:

   These tables are represented as '#tablename'  lives up to session time, once session is closed tables will be deleted automatically.   User cannot access these tables in another session.
  Note: temporary table can be deleted using Drop table #tablename;

select * into  #tbl  from emp;

Table structure and data will be copied to #tbl . this table located in Temp DB.

Manual Dropping of local temporary tables.

drop table #tbl.

Global Temporary tables.

             Global temporary tables also same as Local temporary tables, which are denoted as 

##tablename in the beginning.  It can be accessed in another query wizard, once all sessions/query wizards/connections are closed, these tables are automatically dropped.

select * into ##tbl2 from emp;

Manual Dropping of Global temporary tables.

drop table ##tablename.

TAGS:Temporary Tables in SQL SERVER,SQL Server temporary tables, SQL Server local temporary tables, SQL Server global temporary tables, Dropping temporary tables,Dropping local temporary table, dropping global temporary tables.Select into clause, Creating temporary table using Select into clause.