SQL Temporary Tables

Introduction

In this chapter, we will learn about temporary tables in SQL. Temporary tables are useful for storing intermediate results temporarily within a session. They are automatically dropped when the session ends, making them ideal for complex queries and data transformations that do not need to persist. This chapter will cover the syntax, usage, and examples to help you understand how to use temporary tables effectively.

What are Temporary Tables?

Temporary tables are special types of tables that are created and used within the context of a single database session. They are automatically dropped at the end of the session or when the connection is closed.

Characteristics of Temporary Tables

• Scope: Temporary tables are only accessible within the session that created them.
• Lifetime: They exist only for the duration of the session and are dropped automatically when the session ends.
• Naming: Temporary tables are typically prefixed with # or ## (in SQL Server) or created using the TEMPORARY keyword (in MySQL).

Syntax for Temporary Tables

Creating a Temporary Table (MySQL)

CREATE TEMPORARY TABLE temp_table_name (
    column1 datatype,
    column2 datatype,
    ...
);

Creating a Temporary Table (SQL Server)

CREATE TABLE #temp_table_name (
    column1 datatype,
    column2 datatype,
    ...
);

Step-by-Step Example

1. Create a Temporary Table

MySQL

CREATE TEMPORARY TABLE temp_employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

SQL Server

CREATE TABLE #temp_employees (
    id INT PRIMARY KEY IDENTITY(1,1),
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

2. Insert Sample Data

MySQL

INSERT INTO temp_employees (first_name, last_name, email)
VALUES ('Ramesh', 'Kumar', 'ramesh.kumar@example.com'),
       ('Sita', 'Patel', 'sita.patel@example.com');

SQL Server

INSERT INTO #temp_employees (first_name, last_name, email)
VALUES ('Ramesh', 'Kumar', 'ramesh.kumar@example.com'),
       ('Sita', 'Patel', 'sita.patel@example.com');

3. View the Data

MySQL

SELECT * FROM temp_employees;

SQL Server

SELECT * FROM #temp_employees;

4. Drop the Temporary Table

Temporary tables are automatically dropped when the session ends. However, you can also drop them explicitly if needed.

MySQL

DROP TEMPORARY TABLE temp_employees;

SQL Server

DROP TABLE #temp_employees;

Conclusion

Temporary tables are used for managing intermediate results and complex queries within a single session. They help simplify queries, improve performance, and ensure that temporary data does not persist beyond its intended scope. Understanding how to create, use, and drop temporary tables is essential for effective SQL programming.