Temporary tables in Sql Server

This Article helps you to Understand Temporary tables in Sql Server

Creating temporary table
SQL Server provided 2 ways to create temporary tables via SELECT INTO Or CREATE TABLE statements.

Create temporary tables using SELECT INTO statement

SELECT
    countryName,countryValue
INTO
    #country
FROM
    country
where countryName like 'A%'

In above case, we generated a temporary table called #country, whose 2 columns came from the SELECT statement's select list. The temporary table was established by the statement, which also filled it with data from the country table

Location of  Temporary Table Created above
Once you execute the statement, you can find the temporary table name created in the system database named as below. 

As you can see clearly from the picture, the temporary table also consists of a sequence of numbers as a postfix. This is a unique identifier for the temporary table. Because multiple database connections can create temporary tables with the same name, SQL Server automatically appends this unique number at the end of the temporary table name to differentiate between the temporary tables.

Create temporary tables using CREATE TABLE statement
This can be a Local Temp Table (Accessable in current session only) or Global Temp Table

A. Local Temp Table (Accessable in current session only)

CREATE TABLE #Company (
    id bigint,
    commpanyName VARCHAR(MAX),
    IsActive bit
);

This statement has the same syntax as creating a regular table. However, the name of the temporary table starts with a hash symbol (#)
you can see same in Here: -

This get automatically removed as and when we close that Query Window/ Session

Insert data into above temporary table as a regular table

INSERT INTO #Company
SELECT
    id,
    ComanyName,
    IsActive
FROM
    Company
WHERE
    IsActive = 1;

Query data against above temporary table within the current session

SELECT
    *
FROM
    #Company;

However, if you open another connection (New Query Window) and try the query above query, you will get the following error:
Invalid object name '#Company'.

B. Global temporary tables (Accessable accross sessions / connections / query window)
Sometimes, you may want to create a temporary table that is accessible across connections. In this case, you can use global temporary tables.
the name of a global temporary table starts with a double hash symbol (##).

CREATE TABLE ##Company (
    id bigint,
    commpanyName VARCHAR(MAX),
    IsActive bit
);

Location of Global Temporary Table

Insert data into Global Temporary table

INSERT INTO ##Company
SELECT
    id,
    ComanyName,
    IsActive
FROM
    Company
WHERE
    IsActive = 1;

Query a Global Temporary table

SELECT
    *
FROM
    ##Company;

Dropping temporary tables

• Automatic removal
  SQL Server drops a temporary table automatically when you close the connection that created it.
  SQL Server drops a global temporary table once the connection that created it closed and the queries against this table from other connections completes.
• Manual Deletion
  We can manually remove the temporary table by using the DROP TABLE statement:

  DROP TABLE #Company;
  DROP TABLE ##Company;