CREATING TEMPROARY TABLE IN MSSQL

In Microsoft SQL Server, you can use the SELECT INTO statement to create a temporary table and insert the result set of a query into that table. Temporary tables in SQL Server can be either local or global.

Creating a local temproary table

Syntax:

-- Create a local temporary table and insert data into it
SELECT *
INTO #TempTable
FROM YourSourceTable
WHERE YourCondition;

-- Query the temporary table
SELECT *
FROM #TempTable;

-- Drop the temporary table when you're done
DROP TABLE #TempTable;

#TempTable is the name of the local temporary table.

YourSourceTable is the source table from which data is selected.

YourCondition is an optional condition to filter the data.

Example use case:

Let’s say, we have a long query. I want to compare the results with another table. Temporary tables comes in handy in these situations where we don’t have to create a physical table, yet we can compare the results.

I have taken an example query from Adventureworks2019 database as follows,

 WITH SalesCTE AS (
    SELECT 
        p.Name AS ProductName,
        soh.OrderDate,
        sod.OrderQty,
        sod.UnitPrice,
        sod.LineTotal
    FROM 
        Sales.SalesOrderHeader soh
    JOIN 
        Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID
    JOIN 
        Production.Product p ON sod.ProductID = p.ProductID

      )
SELECT 
    ProductName,
    SUM(LineTotal) AS TotalSales
FROM 
    SalesCTE
GROUP BY ProductName
HAVING SUM(LineTotal) > 1000;

The above query returns us the sum of line total over 1000 per Product.

If we want to compare this list with other list or table values, or simply use this list for other temproary calculation, we can simply copy this query result into a temproary table using #tablename after the select list as shown in the example below.

We can then query simply the temproary table to obtain the query result.

Caution:

Please note, that local temporary tables (#TempTable) are only visible to the session that creates them.

If I open a new session and query the temproary table, I will get the following error saying “Invalid object”.

Creating a Global temproary table:

If you want to create a global temporary table (visible across sessions), you can use ##TempTable:

Syntax:

-- Create a global temporary table and insert data into it
SELECT *
INTO ##GlobalTempTable
FROM YourSourceTable
WHERE YourCondition;

-- Query the global temporary table
SELECT *
FROM ##GlobalTempTable;

-- Drop the global temporary table when you're done
DROP TABLE ##GlobalTempTable;

The same selection above can be created as a global temproary table as follows,

You can now select the same table even if you open a new session.

Dropping the temproary tables:

Make sure you always drop the temproary tables unless you are using them frequently. This will always help to free the space.

To drop the temproary tables, you can use the same drop table command.