Insert Data Into Temp Table with Query

Efficiently Insert Data into Temporary Tables with SQL Queries

Learn various SQL Server techniques to populate temporary tables using query results, enhancing performance and modularity in your database operations.

Temporary tables are a powerful feature in SQL Server, providing a way to store and process intermediate results. They are particularly useful for breaking down complex queries into smaller, more manageable steps, improving readability, and often, performance. This article explores different methods to insert data into temporary tables directly from the results of a SELECT query, covering both local and global temporary tables.

Understanding Temporary Tables in SQL Server

Before diving into insertion methods, it's crucial to understand the two main types of temporary tables in SQL Server: local and global. Each has distinct characteristics regarding scope and visibility.

flowchart TD
    A[SQL Session Start] --> B{Create Temp Table?}
    B -->|Yes, Local (#temp)| C[Local Temp Table Scope]
    C --> D{Visible only to current session}
    D --> E[Dropped on session end]
    B -->|Yes, Global (##temp)| F[Global Temp Table Scope]
    F --> G{Visible to all sessions}
    G --> H[Dropped when last session disconnects]
    A --> I[SQL Session End]

Scope and Lifecycle of Local vs. Global Temporary Tables

Method 1: SELECT INTO - Quick and Easy Creation

The SELECT INTO statement is the most straightforward way to create a new temporary table and populate it with data from a query. It infers the column definitions (data types, nullability) directly from the SELECT statement's result set. This method is excellent for quick prototyping and when you don't need to pre-define the table structure.

SELECT
    CustomerID,
    CompanyName,
    ContactName
INTO
    #CustomersTemp
FROM
    Northwind.dbo.Customers
WHERE
    Country = 'USA';

-- Verify the data
SELECT * FROM #CustomersTemp;

Using SELECT INTO to create and populate a local temporary table

Method 2: CREATE TABLE and INSERT INTO - Explicit Control

For scenarios where you need more control over the temporary table's structure, or if you plan to insert data in multiple steps or from different sources, the CREATE TABLE followed by INSERT INTO approach is preferred. This allows you to define precise data types, constraints, and indexes before any data is loaded.

-- Step 1: Create the temporary table with explicit schema
CREATE TABLE #OrderSummary (
    OrderID INT PRIMARY KEY,
    OrderDate DATE NOT NULL,
    TotalAmount DECIMAL(10, 2),
    CustomerName NVARCHAR(100)
);

-- Step 2: Insert data from a query
INSERT INTO #OrderSummary (OrderID, OrderDate, TotalAmount, CustomerName)
SELECT
    o.OrderID,
    o.OrderDate,
    SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)) AS TotalAmount,
    c.CompanyName
FROM
    Northwind.dbo.Orders AS o
INNER JOIN
    Northwind.dbo."Order Details" AS od ON o.OrderID = od.OrderID
INNER JOIN
    Northwind.dbo.Customers AS c ON o.CustomerID = c.CustomerID
WHERE
    o.OrderDate >= '1997-01-01'
GROUP BY
    o.OrderID, o.OrderDate, c.CompanyName;

-- Verify the data
SELECT * FROM #OrderSummary;

Creating a temporary table with CREATE TABLE and populating it with INSERT INTO

Inserting into Global Temporary Tables

Global temporary tables, prefixed with ##, are visible across all sessions and are dropped only when the last session referencing them disconnects. The insertion methods are identical to local temporary tables, simply by changing the prefix.

-- Using SELECT INTO for a global temporary table
SELECT
    ProductID,
    ProductName,
    UnitPrice
INTO
    ##GlobalProductsTemp
FROM
    Northwind.dbo.Products
WHERE
    UnitsInStock > 0;

-- Using CREATE TABLE and INSERT INTO for a global temporary table
CREATE TABLE ##GlobalEmployees (
    EmployeeID INT PRIMARY KEY,
    FullName NVARCHAR(150),
    HireDate DATE
);

INSERT INTO ##GlobalEmployees (EmployeeID, FullName, HireDate)
SELECT
    EmployeeID,
    FirstName + ' ' + LastName AS FullName,
    HireDate
FROM
    Northwind.dbo.Employees
WHERE
    HireDate < '1994-01-01';

-- Verify data in global temporary tables
SELECT * FROM ##GlobalProductsTemp;
SELECT * FROM ##GlobalEmployees;

Examples of inserting data into global temporary tables