SQL TEMP TABLE

SQL TEMP TABLE provides a standardized way to interact with databases and perform tasks such as querying data, inserting, updating, and deleting records, creating and modifying database structures, and more.

SQL is widely used in the field of data management and plays a crucial role in handling data in various applications and systems. It allows users to retrieve specific information from databases by using queries, and it provides a powerful set of tools for data manipulation, analysis, and reporting.

Temporary tables are pretty much what their name describes: they are the tables that are created in a database to store temporary data. We can perform SQL operations similar to the operations on permanent tables like CREATE, UPDATE, DELETE, INSERT, JOIN, etc. But these tables will be automatically deleted once the current client session is terminated. In addition to that, they can also be explicitly deleted if the users decide to drop them manually.

What is a Temporary SQL Table?

In a database management system, a temporary SQL table also called a temp table, is a table that a session or transaction creates and uses. It’s for storing temporary data needed briefly without needing permanent storage.

Programs create temporary tables on the fly for tasks like complex calculations, storing interim results, or manipulating data subsets during queries.

Temporary tables have a specific scope and lifespan. They’re only accessible within the creating session or transaction. They’re automatically dropped when the session or transaction ends or when explicitly dropped by the user.

Their temporary nature makes them ideal for managing transient data not needing persistence beyond the immediate task.

Temporary tables in SQL offer a convenient way to break down complex problems into smaller, manageable steps. They enable separating data processing stages, potentially improving performance, enhancing code readability, and simplifying query logic.

How to Create a Temporary SQL Table

In SQL, We can create a temporary table using the CREATE TABLE command along with the TEMPORARY or TEMP keyword.

Example

-- creating a temporary course_details table

CREATE TEMPORARY TABLE #course_Details (id INT, course_name VARCHAR(25))

-- inserting values in the temporary table

INSERT INTO #course_Details VALUES (101, 'Python'), (102, 'SQL') 

-- selecting all the rows from temporary table

SELECT * FROM #course_Details 

Output

CREATE TEMPORARY TABLE EMPLOYEE 
(
  emp_name VARCHAR(50) NOT NULL, 
  emp_rate DECIMAL(12,2) NOT NULL DEFAULT 0.00, 
  emp_sal DECIMAL(7,2) NOT NULL DEFAULT 0.00, 
  yoe INT UNSIGNED  DEFAULT 0
);

INSERT INTO EMPLOYEE
   (emp_name, emp_rate, emp_sal, yoe)
   VALUES
   ('ram', 10.25, 9000, 2);

SELECT * FROM EMPLOYEE;

Output

emp_name	    emp_rate	    emp_sal	    yoe
ram	            10.25	        9000	     2

Note: Your temporary table will not appear in the list When you perform the SHOW TABLES command. If you log out of the MySQL session and then submit a SELECT command, you will discover that there is no data in the database, even your temporary table.

Types of Temporary Tables in SQL

There are two major different types of temporary tables in sql:

1. Local Temporary Tables
2. Global Temporary Table

Local Temporary Table in SQL

In SQL, a local temporary table is a temporary table created and existing only for the duration of a session or a transaction. It serves the purpose of storing intermediate results within a session or facilitating complex queries that necessitate temporary storage.

Example

-- Create a local temporary table
CREATE TEMPORARY TABLE #TempEmployees (
    ID INT,
    Name VARCHAR(100),
    Department VARCHAR(100)
);

-- Insert some data into the temporary table
INSERT INTO #TempEmployees (ID, Name, Department) VALUES (1, 'John Doe', 'IT');
INSERT INTO #TempEmployees (ID, Name, Department) VALUES (2, 'Jane Smith', 'HR');
INSERT INTO #TempEmployees (ID, Name, Department) VALUES (3, 'Bob Johnson', 'Finance');

-- Query the temporary table
SELECT * FROM #TempEmployees;

Output

| ID |   Name      | Department |
|----|-------------|------------|
| 1  | John Doe    | IT         |
| 2  | Jane Smith  | HR         |
| 3  | Bob Johnson | Finance    |

Global Temporary Table in SQL

In SQL, a global temporary table is a temporary table accessible to all sessions and remains in existence until explicitly dropped or until the session that created it concludes.

Example

-- Create a global temporary table
CREATE GLOBAL TEMPORARY TABLE TempEmployees (
    ID INT,
    Name VARCHAR(100),
    Department VARCHAR(100)
);

-- Insert some data into the temporary table
INSERT INTO TempEmployees (ID, Name, Department) VALUES (1, 'John Doe', 'IT');
INSERT INTO TempEmployees (ID, Name, Department) VALUES (2, 'Jane Smith', 'HR');
INSERT INTO TempEmployees (ID, Name, Department) VALUES (3, 'Bob Johnson', 'Finance');

-- Query the temporary table
SELECT * FROM TempEmployees;

Output

| ID |   Name      | Department |
|----|-------------|------------|
| 1  | John Doe    | IT         |
| 2  | Jane Smith  | HR         |
| 3  | Bob Johnson | Finance    |

Differences between Temporary and Permanent Tables in SQL

Conclusion

In conclusion, temporary tables in SQL, whether local or global, offer a convenient way to store intermediate results or temporary data within a session or transaction.

• Local Temporary Tables: These are accessible only within the session or transaction that created them. They are automatically dropped when the session ends or when the transaction completes. They are useful for storing data temporarily during complex queries or procedures.
• Global Temporary Tables: These are visible to all sessions but the data within them is session-specific. Each session can only see and modify its own data within the global temporary table.

Both types of temporary tables can greatly enhance the performance and readability of SQL code by providing a structured way to store and manipulate temporary data.

Frequently Asked Questions