SQL Temporary Table

SQL Temporary Table

A SQL Temporary Table is a special type of table that is used to store temporary data. These tables are often used for intermediate steps in data processing, such as holding results of complex queries or calculations. Temporary tables are session-specific, meaning they exist only during the session in which they are created and are automatically dropped when the session ends.

---

Syntax

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

• CREATE TEMPORARY TABLE: This is the SQL keyword used to create a temporary table.
• temp_table_name: This specifies the name of the temporary table you want to create.
• column1, column2, column3, ...: These are the names of the columns in the table.
• datatype: This specifies the type of data the column can hold, such as INTEGER, VARCHAR, DATE, etc.

---

Example

Let's go through a complete example that includes creating a database, creating a table, inserting data into the table, creating a temporary table, and inserting data into the temporary table.

Step 1: Creating a Database

This step involves creating a new database where the table will be stored.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating a Table

In this step, we create a table named employees within the previously created database.

USE example_db;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    hire_date DATE
);

Here, we define the employees table with columns for id, first_name, last_name, email, and hire_date. The id column is set as the primary key and will auto-increment.

Step 3: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('John', 'Doe', 'john.doe@example.com', '2023-01-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Jane', 'Smith', 'jane.smith@example.com', '2023-02-01');

Here, we insert two rows of data into the employees table.

Step 4: Creating a Temporary Table

This step involves creating a temporary table named temp_employees to store temporary data.

CREATE TEMPORARY TABLE temp_employees (
    temp_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    hire_date DATE
);

Here, we define the temp_employees table with columns similar to the employees table.

Step 5: Inserting Data into the Temporary Table

This step involves inserting some data into the temp_employees table.

INSERT INTO temp_employees (first_name, last_name, email, hire_date) VALUES ('Alice', 'Brown', 'alice.brown@example.com', '2023-03-01');
INSERT INTO temp_employees (first_name, last_name, email, hire_date) VALUES ('Bob', 'Green', 'bob.green@example.com', '2023-04-01');

Here, we insert two rows of data into the temp_employees table.

The temp_employees table will be automatically dropped when the session ends, ensuring that it only holds data temporarily.