SQL Server Insert Into Temp Table

Knowing temporary table operations is one of the most crucial skills for any serious database developer. In this comprehensive guide, I’ll share everything I’ve learned about inserting data into temporary tables in SQL Server, which can significantly improve your application’s performance.

Table of Contents

SQL Server Insert Into Temp Table

Understanding the various types of temporary tables is crucial for making informed architectural decisions.

Table Type	Scope	Naming Convention	Storage Location	Visibility
Local Temp (#)	Session-specific	#TableName	tempdb	Current session only
Global Temp (##)	Server-wide	##TableName	tempdb	All sessions
Table Variables	Batch/procedure	@TableName	Memory/tempdb	Current batch only
Common Table Expressions	Query-specific	WITH clause	Memory	Single query

Method 1: SELECT INTO Statement

The SELECT INTO statement has been my go-to method for quickly creating and populating temporary tables.

-- Basic SELECT INTO syntax
SELECT 
    column1,
    column2,
    column3
INTO #TempTableName
FROM SourceTable
WHERE conditions;

Advantages of SELECT INTO

Based on my implementations for companies in Las Vegas, Nashville, and Portland:

Performance Benefits:

Fastest method for bulk data transfer
Minimal transaction log overhead
Automatic data type inference
Optimal initial storage allocation

Development Benefits:

Simple syntax requiring minimal code
Automatic table structure creation
No need to predefine columns
Immediate data availability

Example

Let us first create the table using the query below.

CREATE TABLE #temp_subscriptionsN (
    temp_id INT IDENTITY(1,1),
    subscription_id INT,
    customer_name NVARCHAR(100),
    subscription_start_date DATE,
    subscriber_category NVARCHAR(50),
    billing_country NVARCHAR(50),
    status NVARCHAR(20)
);

The temp table has been created successfully after executing the above query, as shown in the screenshot below.

Now, let us execute the query below to insert data from the source table.

INSERT INTO #temp_subscriptionsN (subscription_id, customer_name, subscription_start_date, subscriber_category, billing_country, status)
SELECT 
    subscription_id,
    customer_name,
    subscription_start_date,
    subscriber_category,
    billing_country,
    status
FROM subscriptionsN

The data has been inserted successfully from the source table, as shown in the screenshot below.

SELECT INTO Best Practices

Through optimizing systems for healthcare networks spanning multiple states, I’ve developed these guidelines:

-- Best practice: Include meaningful column aliases
SELECT 
    e.EmployeeID,
    e.FirstName + ' ' + e.LastName AS FullName,
    e.HireDate,
    d.DepartmentName,
    e.Salary
INTO #ActiveEmployees
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE e.Status = 'Active'
    AND e.HireDate >= '2020-01-01';

Method 2: Traditional INSERT INTO Approach

The traditional INSERT INTO approach offers superior control over the insertion process.

Pre-Creating the Temporary Table

-- Step 1: Create the temporary table structure
CREATE TABLE #EmployeeReport (
    EmployeeID INT NOT NULL,
    FullName VARCHAR(100) NOT NULL,
    Department VARCHAR(50),
    Salary DECIMAL(10,2),
    HireDate DATE,
    INDEX IX_EmployeeID NONCLUSTERED (EmployeeID)
);

After executing the above query, I got the expected output as shown in the screenshot below.

-- Step 2: Insert data using INSERT INTO
INSERT INTO #EmployeeReport (EmployeeID, FullName, Department, Salary, HireDate)
SELECT 
    e.EmployeeID,
    e.FirstName + ' ' + e.LastName,
    d.DepartmentName,
    e.Salary,
    e.HireDate
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE e.Status = 'Active';

Benefits of the Pre-Creation Approach

This approach proved essential for:

Index Optimization: Creating indexes before data insertion
Constraint Enforcement: Implementing business rules at the table level
Data Type Control: Ensuring consistent data types across operations
Memory Management: Better control over storage allocation
Error Handling: More precise error detection and handling

Advanced INSERT Techniques

Batch Processing for Large Datasets:

-- Processing data in batches to avoid transaction log issues
DECLARE @BatchSize INT = 10000;
DECLARE @RowsProcessed INT = 0;
DECLARE @TotalRows INT;

SELECT @TotalRows = COUNT(*) FROM LargeSourceTable WHERE ProcessingDate = '2024-01-15';

WHILE @RowsProcessed < @TotalRows
BEGIN
    INSERT INTO #ProcessedData (ID, ProcessedValue, ProcessingDate)
    SELECT TOP (@BatchSize)
        ID,
        UPPER(LTRIM(RTRIM(RawValue))),
        GETDATE()
    FROM LargeSourceTable
    WHERE ProcessingDate = '2024-01-15'
        AND ID > @RowsProcessed
    ORDER BY ID;
    
    SET @RowsProcessed = @RowsProcessed + @BatchSize;
END;

Method 3: Table Variables vs Temporary Tables

Making the Right Choice

Choosing between table variables and temporary tables.

Table Variable Approach

-- Declaring and populating a table variable
DECLARE @TempResultsN TABLE (
    CustomerID INT,
    OrderTotal DECIMAL(10,2),
    OrderDate DATE
);

INSERT INTO @TempResultsN (CustomerID, OrderTotal, OrderDate)
SELECT 
    CustomerID,
	SUM(OrderTotal),
    OrderDate
FROM CustomerOrdersN
GROUP BY CustomerID, OrderDate;

After executing the above query, I got the expected output as shown in the screenshot below.

sql server insert into temp table from select

Choose Table Variables When:

Working with small datasets (< 1000 rows)
Need simple, fast operations
Want to avoid tempdb contention
Working within stored procedures or functions

Choose Temporary Tables When:

Processing large datasets (> 1000 rows)
Need statistics for query optimization
Require custom indexes
Building complex, multi-step operations

Performance Optimization Strategies

Indexing Strategies for Temporary Tables

During my optimization work for a logistics company with operations from Los Angeles to New York, I developed these indexing strategies:

-- Creating optimized temporary table with strategic indexes
CREATE TABLE #OrderProcessing (
    OrderID INT NOT NULL,
    CustomerID INT NOT NULL,
    OrderDate DATE NOT NULL,
    OrderAmount DECIMAL(10,2),
    Status VARCHAR(20),
    
    -- Clustered index on most frequently filtered column
    INDEX CIX_OrderDate CLUSTERED (OrderDate),
    
    -- Non-clustered indexes for JOIN operations
    INDEX IX_CustomerID NONCLUSTERED (CustomerID),
    INDEX IX_OrderID NONCLUSTERED (OrderID) INCLUDE (OrderAmount, Status)
);

Memory and Storage Optimization

tempdb Configuration Best Practices:

Multiple Data Files: Configure tempdb with multiple data files (typically one per CPU core)
Proper Sizing: Set appropriate initial size and growth settings
Separate Storage: Place tempdb on fast storage (SSD preferred)
Monitor Usage: Regularly monitor tempdb space utilization

Conclusion:

The key principles I want you to take away from this comprehensive guide are:

Choose the Right Tool for the Job:

Use SELECT INTO for fast, simple data transfer operations
Use INSERT INTO when you need precise control over table structure
Choose table variables for small, simple datasets
Opt for temporary tables for complex, large-scale operations

Optimize for Performance:

Create appropriate indexes before bulk data insertion
Process large datasets in manageable batches
Monitor tempdb usage and configure it properly
Implement proper cleanup and error handling

Follow Best Practices:

Explicitly drop temporary tables when appropriate
Update statistics on large temporary tables
Use meaningful naming conventions
Implement proper error handling and logging

You may also like the following articles

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.