How to create Temp table with SELECT * INTO tempTable FROM CTE Query

Creating Temporary Tables from CTEs in SQL Server

Learn how to efficiently create temporary tables directly from Common Table Expressions (CTEs) using the SELECT * INTO statement in SQL Server, enhancing query readability and performance.

Common Table Expressions (CTEs) are powerful features in SQL that improve the readability and maintainability of complex queries. They allow you to define a temporary, named result set that you can reference within a single SELECT, INSERT, UPDATE, or DELETE statement. While CTEs are temporary by nature and only exist for the duration of the query, there are scenarios where you might need to persist their results into a temporary table for further processing, indexing, or to break down a very complex query into manageable steps. This article will guide you through the process of creating a temporary table directly from a CTE using the SELECT * INTO #tempTable FROM CTE syntax in SQL Server.

Understanding CTEs and Temporary Tables

Before diving into the combination, let's briefly review what CTEs and temporary tables are and why they are used. A CTE is defined using the WITH clause and provides a way to define a logical, non-persisted result set. Temporary tables, on the other hand, are physical tables stored in the tempdb database that exist for the duration of a session or transaction. They are useful for storing intermediate results that might be too large for memory or need to be accessed multiple times within a session.

flowchart TD
    A[Start Query] --> B{Define CTE (WITH clause)}
    B --> C[CTE Result Set (Logical)]
    C --> D{Need to Persist Results?}
    D -- Yes --> E[Create Temp Table (SELECT * INTO #tempTable)]
    E --> F[Temp Table (Physical in tempdb)]
    F --> G[Further Operations on Temp Table]
    D -- No --> H[Directly Use CTE in Main Query]
    H --> I[End Query]
    G --> I

Flowchart illustrating the decision process for using a temporary table from a CTE.

Creating a Temporary Table from a CTE

The most straightforward way to create a temporary table from the result of a CTE in SQL Server is to use the SELECT * INTO #tempTable FROM CTE_Name syntax immediately after defining your CTE. This approach creates a new temporary table with the same schema as the CTE's result set and populates it with the CTE's data. This method is often preferred for its simplicity and efficiency, as it avoids the need to pre-define the temporary table's schema.

WITH EmployeeSales AS (
    SELECT
        e.EmployeeID,
        e.FirstName,
        e.LastName,
        SUM(soh.TotalDue) AS TotalSales
    FROM
        HumanResources.Employee AS e
    JOIN
        Sales.SalesOrderHeader AS soh ON e.EmployeeID = soh.SalesPersonID
    GROUP BY
        e.EmployeeID, e.FirstName, e.LastName
    HAVING
        SUM(soh.TotalDue) > 100000
)
SELECT * INTO #TopEmployeeSales
FROM EmployeeSales;

-- Now you can query the temporary table
SELECT * FROM #TopEmployeeSales
WHERE TotalSales > 500000;

-- Don't forget to drop the temporary table when done (optional, as it drops automatically at session end)
DROP TABLE IF EXISTS #TopEmployeeSales;

Example of creating a temporary table from a CTE in SQL Server.

Advantages and Considerations

Using SELECT * INTO with CTEs offers several advantages:

• Simplicity: It's a concise way to create and populate a temporary table in one step.
• Performance: For large result sets, materializing the CTE into a temporary table can sometimes improve performance, especially if the CTE is complex or needs to be scanned multiple times.
• Indexing: Once the data is in a temporary table, you can add indexes to it, which can significantly speed up subsequent queries against that data.
• Debugging: It allows you to inspect intermediate results of a complex CTE, aiding in debugging.

However, there are also considerations:

• Disk I/O: Creating a physical temporary table involves writing data to tempdb, which can incur disk I/O overhead.
• Scope: Local temporary tables (#tempTable) are only visible to the current session and are automatically dropped when the session ends. Global temporary tables (##tempTable) are visible to all sessions and persist until all sessions referencing them disconnect.
• Schema Changes: If the CTE's schema changes, the temporary table created by SELECT * INTO will automatically adapt. If you had pre-defined the temporary table, you would need to alter its schema manually.

1. Define Your CTE

Start by defining your Common Table Expression using the WITH clause. Ensure your CTE produces the desired result set that you wish to store in a temporary table.

2. Use SELECT * INTO

Immediately after your CTE definition, use the SELECT * INTO #YourTempTableName FROM YourCTE_Name; statement. Replace #YourTempTableName with your desired temporary table name and YourCTE_Name with the name of your CTE.

3. Query the Temporary Table

Once created, you can query, join, or perform any DML operations on your temporary table just like a regular table. Remember its scope is limited to your current session.

4. Clean Up (Optional)

Although temporary tables are automatically dropped when the session ends, it's good practice to explicitly DROP TABLE IF EXISTS #YourTempTableName; when you are finished with it, especially in long-running scripts or stored procedures, to free up tempdb resources sooner.