What is a temp table?

A temp table is a table exists on the SQL Server temporarily.

In MS SQL Server, there are two types of temp table: Local Temporary Table and Global Temporary Table. The former is denoted with a single hashtag (#) before the table name while the latter is denoted as a double hashtags (##) before the table name.

A local temp table gets dropped automatically when the session that created it ends.

A global temp table gets dropped automatically when the session that created it ends and there is no active references to it.

You can explicitly drop a temp table.

Create a temp table

Two ways of creating a temp table:

Using SELECT INTO statement

Example:

SELECT CustomerID, SUM(Amount) TotalOrderAmount
INTO #TotalOrder
FROM [Order]
GROUP BY CustomerID

Using CREATE TABLE statement

Example:

-- Create the temp table
CREATE TABLE #TotalOrder (
	[CustomerID] [int] NOT NULL,
	[TotalOrderAmount] [money] NULL
)

-- Insert values to the temp table
INSERT INTO #TotalOrder
SELECT CustomerID, SUM(Amount) TotalOrderAmount
FROM [Order]
GROUP BY CustomerID

Where does a temp table resides?

Under Databases > System Databases > tempdb > Temporary Tables:

Drop a temp table

Example:

DROP TABLE #TotalOrder