Is it possible to apply SELECT INTO a temporary table from another SELECT?

Creating Temporary Tables from SELECT Statements in SQL Server

Explore various methods to populate temporary tables directly from SELECT statements in SQL Server, including SELECT INTO, INSERT INTO ... SELECT, and Common Table Expressions (CTEs).

In SQL Server, temporary tables are invaluable for storing intermediate results, simplifying complex queries, and improving performance. A common requirement is to populate these temporary tables directly from the results of another SELECT statement. This article delves into the primary methods for achieving this, providing practical examples and discussing their nuances.

Method 1: SELECT INTO - The Simplest Approach

The SELECT INTO statement is the most straightforward and often the most efficient way to create a new table (including temporary tables) and populate it with data from a SELECT query. It creates the table structure based on the SELECT list and then inserts the data in a single operation. This method is particularly useful when you don't need to pre-define the temporary table's schema.

SELECT
    CustomerID,
    OrderDate,
    TotalAmount
INTO
    #TempCustomerOrders
FROM
    Sales.Orders
WHERE
    OrderDate >= '2023-01-01';

Using SELECT INTO to create and populate a local temporary table.

Method 2: INSERT INTO ... SELECT - For Existing Temporary Tables

When you need to populate an already existing temporary table, or if you want to control the schema explicitly before inserting data, INSERT INTO ... SELECT is the preferred method. This approach requires you to first define the temporary table's structure using CREATE TABLE and then use INSERT INTO to pull data from a SELECT statement.

CREATE TABLE #TempProductSales (
    ProductID INT,
    ProductName NVARCHAR(100),
    TotalQuantitySold INT
);

INSERT INTO #TempProductSales (ProductID, ProductName, TotalQuantitySold)
SELECT
    p.ProductID,
    p.Name AS ProductName,
    SUM(od.OrderQty) AS TotalQuantitySold
FROM
    Production.Product AS p
JOIN
    Sales.SalesOrderDetail AS od ON p.ProductID = od.ProductID
GROUP BY
    p.ProductID, p.Name
HAVING
    SUM(od.OrderQty) > 1000;

Using CREATE TABLE followed by INSERT INTO ... SELECT to populate an existing temporary table.

flowchart TD
    A[Start]
    A --> B{Need new table structure?}
    B -- Yes --> C[SELECT ... INTO #TempTable]
    B -- No --> D[CREATE TABLE #TempTable (Schema)]
    D --> E[INSERT INTO #TempTable SELECT ...]
    C --> F[Use #TempTable]
    E --> F[Use #TempTable]
    F --> G[End]

Decision flow for choosing between SELECT INTO and INSERT INTO ... SELECT.

Method 3: Using Common Table Expressions (CTEs)

While not directly creating a temporary table, Common Table Expressions (CTEs) offer a powerful alternative for breaking down complex queries into logical, readable steps. CTEs define a named temporary result set that you can reference within a single SELECT, INSERT, UPDATE, or DELETE statement. They are often used to achieve similar goals as temporary tables without the overhead of physical table creation.

WITH TopSellingProducts AS (
    SELECT
        p.ProductID,
        p.Name AS ProductName,
        SUM(od.OrderQty) AS TotalQuantitySold
    FROM
        Production.Product AS p
    JOIN
        Sales.SalesOrderDetail AS od ON p.ProductID = od.ProductID
    GROUP BY
        p.ProductID, p.Name
    HAVING
        SUM(od.OrderQty) > 500
)
SELECT
    tsp.ProductName,
    tsp.TotalQuantitySold
FROM
    TopSellingProducts AS tsp
ORDER BY
    tsp.TotalQuantitySold DESC;

Using a CTE to define a temporary result set for a subsequent query.