Create Temporary Table SQL

Temporary tables are used to store data temporarily while a query is being executed. They are deleted automatically when the session is closed or the query is completed. In this article, we will discuss how to create temporary tables in SQL.

Creating a Temporary Table

To create a temporary table in SQL, you can use the following syntax:

CREATE TEMPORARY TABLE table_name (
  column1 data_type,
  column2 data_type,
  column3 data_type,
 ....
);

Here is an example of creating a temporary table:

CREATE TEMPORARY TABLE temp_employees (
  employee_id INT,
  name VARCHAR(255),
  salary DECIMAL(10, 2)
);

Note: The TEMPORARY keyword is used to specify that the table is a temporary table.

Creating a Temporary Table from a Select Statement

You can also create a temporary table from a select statement using the following syntax:

CREATE TEMPORARY TABLE table_name AS
SELECT column1, column2, column3,...
FROM existing_table;

Here is an example of creating a temporary table from a select statement:

CREATE TEMPORARY TABLE temp_employees AS
SELECT employee_id, name, salary
FROM employees
WHERE department = 'Sales';

Key Points:

• Temporary tables are deleted automatically when the session is closed or the query is completed.
• Temporary tables can be used to store data temporarily while a query is being executed.
• The TEMPORARY keyword is used to specify that the table is a temporary table.
• Temporary tables can be created from a select statement using the CREATE TEMPORARY TABLE syntax.

Examples:

Example Use Case

Suppose we have a table called employees with columns employee_id, name, and salary. We want to create a temporary table to store the employees who work in the sales department.

CREATE TEMPORARY TABLE temp_employees AS
SELECT employee_id, name, salary
FROM employees
WHERE department = 'Sales';

We can then use the temporary table to perform queries, such as:

SELECT * FROM temp_employees;

This will return the employees who work in the sales department.

Technical Specifications:

• Database management system: MySQL
• SQL version: 8.0
• Temporary table storage: In-memory storage

Frequently Asked Questions:

In conclusion, temporary tables are a useful feature in SQL that can be used to store data temporarily while a query is being executed. They can be created using the CREATE TEMPORARY TABLE syntax and can be used to improve the performance of complex queries.