SQL Server Temporary Table

Introduction

       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

       Similarly, you can SELECT, INSERT, and UPDATE like any other table, but remember the prefix is part

       of the table name.

Deciding between Local and Global

       When deciding which type of table to use ask yourself two questions. First, "Do I need this data to persist
       when I am done using it?" If so, I need a standard table, not a temporary table. Second, Do I need the data to
       be accessed outside of my single process?" This question can sometimes be a little tougher to figure out, so I
       have a simple suggestion. Make it a local temporary table for now, and if you find out you need a larger scope,
       change it later. In today's world there are plenty of tools to find and replace, so there's no reason to start
       with a global temporary table unless you know you will need it.

Real World

       In the real world there are several uses for temporary tables. Many people find it easier to follow their
       work if they create a series of tables and select the data from them to get to their final result. Most of the
       time this will be slower than a few good joins, but not always. Besides, getting started you might find it
       easier to follow the process of gathering your data.

       Another use for temporary tables is when you will have multiple users accessing your system and you need
       to keep track of temporary user information. An example you would probably be familiar with would be a

       shopping cart on a web application. You could collect a list of items people are purchasing, and if they
       choose to leave the site rather than checking out, the shopping cart will just go away. Using local temporary 
       tables you eliminate the worry of customers seeing another user's shopping cart because their session has no
       access to it.

Conclusion

       This has been a relatively high level overview of temporary tables in SQL Server. If you are looking for
        more in-depth information, do a search for SQL Server temporary tables with your favorite search engine.