SQL Server Create Temp Table From Select

by

Creating temporary tables from SELECT statements in SQL Server can be accomplished using SELECT INTO or CREATE TABLE followed by INSERT INTO syntax, with temporary tables starting with # for local scope or ## for global scope.

SQL Server Create Temp Table From Select

Understanding SQL Server Temporary Tables

What Are Temporary Tables in SQL Server?

Temporary tables are essential tools for intermediate data storage, complex query optimization, and batch processing operations that support critical business functions.

Temporary Table Fundamentals for Database Development:

Temporary Table Type Naming ConventionScope and VisibilityStorage LocationUse Case Scenario
Local Temp Table#TempTableNameCurrent session onlytempdb databaseUser-specific processing
Global Temp Table##TempTableNameAll sessionstempdb databaseCross-session data sharing
Table Variable@TableVariableCurrent batchMemory/tempdbSmall dataset operations
Common Table Expression (CTE)WITH CTENameSingle queryVirtual result setQuery readability enhancement

Creating Temp Tables Using SELECT INTO

Basic SELECT INTO Syntax

Simplest Method for American Development Teams:

SELECT INTO represents the most straightforward approach for creating temporary tables from existing data:

SELECT INTO Pattern:

Case-1: Basic SELECT INTO temporary table pattern for applications

SELECT 
    CustomerID,
    CustomerName,
    City,
    State,
    ZipCode
INTO #USACustomers
FROM Customers
WHERE Country = 'USA';

Case-2: SELECT INTO with join operations

SELECT 
    o.OrderID,
    c.CustomerName,
    o.OrderDate,
    o.TotalAmount
INTO #USAOrderSummary
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE c.Country = 'USA'
    AND o.OrderDate >= '2025-01-01';

Case-3: SELECT INTO with aggregations

SELECT 
    State,
    City,
    COUNT(*) AS CustomerCount,
    SUM(TotalPurchases) AS TotalRevenue,
    AVG(CustomerLifetimeValue) AS AverageLTV
INTO #StateCustomerMetrics
FROM CustomerAnalytics
WHERE Country = 'USA'
GROUP BY State, City;

SELECT INTO Advanced Techniques

SELECT INTO TechniqueBusiness ApplicationPerformance BenefitCode Complexity
Filtered SELECT INTOregional data segmentationReduced dataset sizeLow complexity
Computed Columnsbusiness calculation logicPre-calculated valuesMedium complexity
Multiple Table Joinsdenormalized reportingQuery performance improvementMedium complexity
UNION Operationsdata consolidationCombined dataset creationMedium complexity
Subquery Integrationcomplex filtering logicOptimized data retrievalHigh complexity

Advanced SELECT INTO Examples:

-- SELECT INTO with UNION
SELECT 
    'East Coast' AS Region,
    CustomerID,
    OrderTotal
INTO #RegionalSales
FROM EastCoastOrders
WHERE OrderDate >= '2025-01-01'
UNION ALL
SELECT 
    'West Coast' AS Region,
    CustomerID,
    OrderTotal
FROM WestCoastOrders
WHERE OrderDate >= '2025-01-01';

-- SELECT INTO with window functions 
SELECT 
    CustomerID,
    OrderDate,
    OrderAmount,
    ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS OrderSequence,
    SUM(OrderAmount) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RunningTotal
INTO #CustomerOrderHistory
FROM Orders
WHERE CustomerState IN ('CA', 'NY', 'TX', 'FL');

-- SELECT INTO with CASE statements
SELECT 
    CustomerID,
    TotalPurchases,
    CASE 
        WHEN TotalPurchases >= 10000 THEN 'Premium'
        WHEN TotalPurchases >= 5000 THEN 'Gold'
        WHEN TotalPurchases >= 1000 THEN 'Silver'
        ELSE 'Bronze'
    END AS CustomerTier
INTO #CustomerSegmentation
FROM CustomerMetrics
WHERE Country = 'USA';

Conclusion:

Mastering SQL Server temporary table creation from SELECT statements extends far beyond understanding basic syntax—it requires comprehensive knowledge of performance optimization techniques, resource management strategies, and architectural best practices that define successful database development.

  • Multiple Creation Approaches: Understanding both SELECT INTO and CREATE TABLE methodologies enables developers to choose the optimal approach based on specific requirements, with SELECT INTO providing rapid development for straightforward scenarios and explicit CREATE TABLE offering granular control for complex enterprise applications.

You may also like the following articles: