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:
- Automatic Deletion: Temporary tables are automatically removed from the database when the session ends, making them suitable for short-term data manipulation tasks.
- Data Pre-processing: They serve as a holding area for storing intermediate results during complex calculations, enhancing the efficiency of data processing.
- Data Staging: Temporary tables can collect results from multiple queries, facilitating subsequent analysis or data merging operations.
- 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) namedhigh_sales
.SELECT *
: It selects all columns from theSales
table.FROM Sales
: It specifies the source table asSales
.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 namedmonthly_revenue
.SELECT EXTRACT(MONTH FROM OrderDate) AS Month
: Extracts the month from theOrderDate
column and aliases it asMonth
.SUM(Amount) AS TotalRevenue
: Calculates the total revenue.FROM Orders
: Specifies the source table asOrders
.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 theProducts
table.INTO HighValueProducts
: Creates a new table namedHighValueProducts
.FROM Products
: Specifies the source table asProducts
.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 theEmployees
table.INTO EmployeesBackup
: Creates a new table namedEmployeesBackup
.FROM Employees
: Specifies the source table asEmployees
.
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 namedhigh_sales
.SELECT * FROM Sales WHERE Amount > 1000
: Selects and filters records from theSales
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 namedcustomer_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 namedmonthly_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 theOrders
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 namedemployee_backup
.SELECT * FROM Employees
: Selects all columns and data from theEmployees
table.
Best Practices for Working with Temporary Tables
- 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.
- 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.