Creating SQL Temporary Tables

Last Updated on: 1st March 2024, 05:58 pm

Temporary tables in SQL provide a valuable tool for managing and manipulating data within a database session without the need for permanent storage. They serve various purposes such as data pre-processing, staging, and filtering subsets of data.

What are Temporary Tables?

Temporary tables, as the name suggests, are temporary storage structures that exist only for the duration of a database session. They are automatically deleted when the session ends. Here’s a quick recap of what you need to know about temporary tables:

1. Automatic Deletion: Temporary tables are automatically removed from the database when the session ends, making them suitable for short-term data manipulation tasks.
2. Data Pre-processing: They serve as a holding area for storing intermediate results during complex calculations, enhancing the efficiency of data processing.
3. Data Staging: Temporary tables can collect results from multiple queries, facilitating subsequent analysis or data merging operations.
4. Filtered Subset Storage: They can store filtered subsets of data, eliminating the need to repeatedly select and filter data for analysis, thereby streamlining data retrieval processes.

Now, let’s explore various methods to create temporary tables, focusing on techniques applicable to BigQuery.

Temporary Table Creation in BigQuery

In BigQuery, temporary tables can be created using the WITH clause. The syntax for creating a temporary table with the WITH clause is as follows:

WITH
    new_table_data AS (
        SELECT *
        FROM Existing_table
        WHERE Tripduration >= 60
    )

Let’s break down this query:

• The statement begins with the WITH clause followed by the name of the new temporary table (new_table_data in this example).
• The AS clause indicates that the subsequent query result will be stored in the temporary table.
• The subquery within the parentheses filters the data from an existing table (Existing_table).
• Once executed, the filtered data will be stored in the temporary table new_table_data, allowing subsequent queries to be run on this filtered dataset.

Creating a Temporary Table in BigQuery:

Consider a scenario where we want to create a temporary table named high_sales containing sales records where the sales amount is greater than $1000:

WITH
    high_sales AS (
        SELECT *
        FROM Sales
        WHERE Amount > 1000
    )

In this example, Sales is the existing table containing sales records, and Amount is the column representing sales amount.

Temporary Table Creation in Other Databases

While the WITH clause is specific to BigQuery, other databases such as SQL Server and MySQL offer alternative methods for creating temporary tables. One common approach is using the SELECT INTO syntax:

SELECT *
INTO AfricaSales
FROM GlobalSales
WHERE Region = "Africa"

In this example, GlobalSales is the existing table containing global sales records, and we are selecting records for the African region and storing them in a temporary table named AfricaSales.

User-Managed Temporary Table Creation

In addition to database-managed temporary tables, users can create and manage temporary tables using the CREATE TABLE statement. After completing the analysis, users can drop these tables using the DROP TABLE clause.

CREATE TEMP TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...
)

Creating a User-Managed Temporary Table:

Suppose we want to create a temporary table named employee_data to store employee information for analysis:

CREATE TEMP TABLE employee_data (
    employee_id INT,
    employee_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10,2)
)

Once the analysis is complete, the temporary table employee_data can be dropped using the following command:

DROP TABLE employee_data

Practice these Examples for understanding the Temporary Tables

Example 1: Creating Temporary Tables with the WITH Clause in BigQuery

-- Example 1.1: Filtering and Storing Specific Data
WITH high_sales AS (
    SELECT *
    FROM Sales
    WHERE Amount > 1000
)

Explanation:

• WITH high_sales AS (: This line starts the common table expression (CTE) named high_sales.
• SELECT *: It selects all columns from the Sales table.
• FROM Sales: It specifies the source table as Sales.
• WHERE Amount > 1000): It filters the data, selecting only records where the amount is greater than $1000.

-- Example 1.2: Calculating Aggregated Metrics
WITH monthly_revenue AS (
    SELECT EXTRACT(MONTH FROM OrderDate) AS Month, SUM(Amount) AS TotalRevenue
    FROM Orders
    GROUP BY Month
)

Explanation:

• WITH monthly_revenue AS (: Begins a CTE named monthly_revenue.
• SELECT EXTRACT(MONTH FROM OrderDate) AS Month: Extracts the month from the OrderDate column and aliases it as Month.
• SUM(Amount) AS TotalRevenue: Calculates the total revenue.
• FROM Orders: Specifies the source table as Orders.
• GROUP BY Month): Groups the results by month.

Example 2: Creating Temporary Tables with SELECT INTO in SQL Server

-- Example 2.1: Storing Data Based on Specific Conditions
SELECT *
INTO HighValueProducts
FROM Products
WHERE UnitPrice > 100

Explanation:

• SELECT *: Selects all columns from the Products table.
• INTO HighValueProducts: Creates a new table named HighValueProducts.
• FROM Products: Specifies the source table as Products.
• WHERE UnitPrice > 100: Filters the data, selecting only records where the unit price is greater than $100.

-- Example 2.2: Copying Structure and Data from an Existing Table
SELECT *
INTO EmployeesBackup
FROM Employees

Explanation:

• SELECT *: Selects all columns from the Employees table.
• INTO EmployeesBackup: Creates a new table named EmployeesBackup.
• FROM Employees: Specifies the source table as Employees.

Example 3: Creating User-Managed Temporary Tables in SQL Server

-- Example 3.1: Defining Table Structure
CREATE TABLE #EmployeeData (
    EmployeeID INT,
    EmployeeName VARCHAR(50),
    Department VARCHAR(50),
    Salary DECIMAL(10,2)
)

Explanation:

• CREATE TABLE #EmployeeData (: Begins the creation of a temporary table named #EmployeeData.
• EmployeeID INT, EmployeeName VARCHAR(50), Department VARCHAR(50), Salary DECIMAL(10,2): Specifies the columns and their data types.

-- Example 3.2: Populating Data into the Temporary Table
INSERT INTO #EmployeeData (EmployeeID, EmployeeName, Department, Salary)
VALUES (1, 'John Doe', 'IT', 60000.00),
       (2, 'Jane Smith', 'HR', 55000.00),
       (3, 'David Lee', 'Finance', 65000.00)

Explanation:

• INSERT INTO #EmployeeData (EmployeeID, EmployeeName, Department, Salary): Specifies the columns to insert data into.
• VALUES: Inserts multiple rows of data into the temporary table.

Example 4: Creating Temporary Tables in PostgreSQL

-- Example 4.1: Storing Filtered Data
CREATE TEMP TABLE high_sales AS
SELECT *
FROM Sales
WHERE Amount > 1000

Explanation:

• CREATE TEMP TABLE high_sales AS: Creates a temporary table named high_sales.
• SELECT * FROM Sales WHERE Amount > 1000: Selects and filters records from the Sales table where the amount is greater than $1000.

-- Example 4.2: Creating a Temporary Table with Defined Structure
CREATE TEMP TABLE customer_info (
    customer_id INT,
    customer_name VARCHAR(100),
    email VARCHAR(255)
)

Explanation:

• CREATE TEMP TABLE customer_info (: Begins the creation of a temporary table named customer_info.
• customer_id INT, customer_name VARCHAR(100), email VARCHAR(255): Specifies the columns and their data types.

Example 5: Creating Temporary Tables in MySQL

-- Example 5.1: Storing Aggregated Data
CREATE TEMPORARY TABLE monthly_revenue
SELECT EXTRACT(MONTH FROM OrderDate) AS Month, SUM(Amount) AS TotalRevenue
FROM Orders
GROUP BY Month

Explanation:

• CREATE TEMPORARY TABLE monthly_revenue: Creates a temporary table named monthly_revenue.
• SELECT EXTRACT(MONTH FROM OrderDate) AS Month, SUM(Amount) AS TotalRevenue FROM Orders GROUP BY Month: Selects and aggregates revenue data by month from the Orders table.

-- Example 5.2: Copying Structure and Data from an Existing Table
CREATE TEMPORARY TABLE employee_backup
SELECT *
FROM Employees

Explanation:

• CREATE TEMPORARY TABLE employee_backup: Creates a temporary table named employee_backup.
• SELECT * FROM Employees: Selects all columns and data from the Employees table.

Best Practices for Working with Temporary Tables

1. Global vs. Local Temporary Tables: Understand the distinction between global and local temporary tables. Local temporary tables are typically used for session-specific operations and are automatically deleted when the session ends.
2. Dropping Temporary Tables After Use: It’s good practice to drop temporary tables after use to release database resources and optimize performance, especially in scenarios involving heavy processing.