Creating Temporary Tables in SQL Server

Temporary tables are a valuable feature in SQL Server that allows you to store and manipulate data temporarily. They can be used to improve performance, simplify complex queries, and provide a temporary storage space for data. In this tutorial, we will explore how to create temporary tables in SQL Server.

Introduction to Temporary Tables

Temporary tables are tables that are created in the tempdb database and are automatically deleted when the session is closed or the table is explicitly dropped. They can be used to store data temporarily while performing complex queries or operations.

There are two types of temporary tables: local temporary tables and global temporary tables. Local temporary tables are prefixed with a single hash symbol (#) and are accessible only within the current session. Global temporary tables are prefixed with two hash symbols (##) and are accessible from any session.

Creating a Temporary Table

To create a temporary table, you can use the CREATE TABLE statement or the SELECT INTO statement.

Using CREATE TABLE Statement

The following example creates a local temporary table named #TempTable:

CREATE TABLE #TempTable (
    Column1 INT,
    Column2 VARCHAR(50),
    Column3 DATETIME
);

You can then insert data into the temporary table using the INSERT INTO statement:

INSERT INTO #TempTable (Column1, Column2, Column3)
VALUES (1, 'Value1', GETDATE());

Using SELECT INTO Statement

The following example creates a local temporary table named #TempTable and populates it with data from a query:

SELECT * INTO #TempTable
FROM (
    SELECT OptionNo, OptionName FROM Options WHERE OptionActive = 1
) AS X;

Note that the SELECT INTO statement creates the temporary table automatically based on the columns in the query.

Creating a Temporary Table from a Common Table Expression (CTE)

To create a temporary table from a CTE, you can use the following syntax:

;WITH Calendar AS (
    SELECT /*... Rest of CTE definition removed for clarity*/
)
SELECT EventID,
       EventStartDate,
       EventEndDate,
       PlannedDate                   AS [EventDates],
       Cast(PlannedDate AS DATETIME) AS DT,
       Cast(EventStartTime AS TIME)  AS ST,
       Cast(EventEndTime AS TIME)    AS ET,
       EventTitle,
       EventType
INTO #TempTable        
FROM   Calendar
WHERE  ( PlannedDate &gt;= Getdate() )
       AND ',' + EventEnumDays + ',' LIKE '%,' + Cast(Datepart(dw, PlannedDate) AS CHAR(1)) + ',%'
        OR EventEnumDays IS NULL
ORDER  BY EventID,
          PlannedDate
OPTION (maxrecursion 0);

Note that the INTO clause is used to specify the name of the temporary table.

Dropping a Temporary Table

To drop a temporary table, you can use the DROP TABLE statement:

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
BEGIN
    DROP TABLE #TempTable;
END

It’s good practice to explicitly drop every temporary table you create to avoid cluttering up the tempdb database.

Best Practices

When working with temporary tables, keep in mind the following best practices:

Use local temporary tables instead of global temporary tables whenever possible.
Avoid using SELECT INTO statement without specifying the columns explicitly.
Always drop temporary tables when they are no longer needed to avoid cluttering up the tempdb database.
Consider using table variables or Common Table Expressions (CTEs) as an alternative to temporary tables in some cases.

By following these guidelines and best practices, you can effectively use temporary tables in SQL Server to improve performance, simplify complex queries, and provide a temporary storage space for data.