Temporary Tables in SQLSERVER

Question

Hi, friends I just want to know that what are temporary tables in SqlServer? Why we used them?

Syed Nayab Ali · Answer

Hi Michell Temporary tables are a useful tool in SQL Server provided to allow for short term use of data. There are two types of temporary table in SQL Server, local and global. Local temporary tables are only available to the current connection to the database for the current user and are dropped when the connection is closed. Global temporary tables are available to any connection once created, and are dropped when the last connection using it is closed. Both types of temporary tables are created in the system database tempdb. Creating 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 a local temporary table, you simply prefix the name with a (#). To make the table a global temporary table, prefix it with (##). -- Create a local temporary table using CREATE TABLE CREATE TABLE #myTempTable ( DummyField1 INT, DummyField2 VARCHAR(20) ) -- Create a local temporary table using SELECT..INTO SELECT age AS DummyField1, lastname AS DummyField2 INTO #myTempTable FROM DummyTable Both of these samples create a local temporary table named #myTempTable with two fields DummyField1 and DummyField2. To make these into global temporary tables, just replace (#) with (##) CREATE TABLE ##myTempTable ( DummyField1 INT, DummyField2 VARCHAR(20) ) SELECT age AS DummyField1, lastname AS DummyField2 INTO ##myTempTable FROM DummyTable Please click on "Answer Accepted" if it helps you

Javeed M Shaikh · Answer

Hi Michell, Temporary tables in SQL Server provides to allow for short term use of data. There are two types of temporary table in SQL Server, local and global. Local temporary tables are only available to the current connection to the database for the current user and are dropped when the connection is closed. Global temporary tables are available to any connection once created, and are dropped when the last connection using it is closed. -- Create a local temporary table using CREATE TABLE CREATE TABLE #myTempTable ( DummyField1 INT, DummyField2 VARCHAR(20) ) -- Create a global temporary table using CREATE TABLE, see ## is the difference CREATE TABLE ##myGTempTable ( DummyField1 INT, DummyField2 VARCHAR(20) )

Temporary Tables in SQLSERVER

Answers (2)