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
SQLOutput
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;
SQLOutput
emp_name emp_rate emp_sal yoe
ram 10.25 9000 2
SQLNote: 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:
- Local Temporary Tables
- 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;
SQLOutput
| ID | Name | Department |
|----|-------------|------------|
| 1 | John Doe | IT |
| 2 | Jane Smith | HR |
| 3 | Bob Johnson | Finance |
SQLGlobal 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;
SQLOutput
| ID | Name | Department |
|----|-------------|------------|
| 1 | John Doe | IT |
| 2 | Jane Smith | HR |
| 3 | Bob Johnson | Finance |
SQLDifferences between Temporary and Permanent Tables in SQL
Basis | Temporary Table | Permanent Table |
Data persistence | In the temporary table, data is not persisted beyond the current session | In the permanent table, data is persisted permanently |
Indexes and constraints | Indexes and constraints are usually temporary, dropped with the table. | It can have indexes, constraints, and triggers. |
Storage allocation | Temporary storage is usually allocated in memory or a temporary storage space. | Permanent storage resides either on disk or in a database.. |
Naming convention | It names are often prefixed with a special character or keyword | Permanent table names lack special character or keyword prefixes. |
Data retention | Data auto-deletes upon session or connection end. | Data persists in the table until intentionally altered or removed. |
Lifespan | It exists only for the current session or connection | It persists even after the session or connection is closed. |
Transactional properties | Transactionality varies across database systems. | They participate in transactions and support ACID properties. |
Accessibility | They are accessible only to the session or connection that created it | They are Accessible to all users and connections with suitable privileges. |
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
Temporary tables are unique tables in SQL, created and utilized temporarily to store data within a session or transaction. They prove especially handy for holding intermediate results or temporary data during complex queries or procedures.
There are two main types of temporary tables: local temporary tables and global temporary tables. Local temporary tables are visible only within the session or transaction that created them, while global temporary tables are visible to all sessions but their data is session-specific.
Local temporary tables are created using the CREATE TEMPORARY TABLE
or CREATE TABLE
statement with a single #
prefix before the table name. They are automatically dropped when the session ends or the transaction completes. Local temporary tables are useful for temporary storage within a single session or transaction.
Temporary tables provide a structured way to store and manipulate temporary data, improving the readability and performance of SQL code. They can help break down complex queries into simpler steps and avoid repetitive calculations by storing intermediate results.