Temp Table - SQL

A temporary table in SQL is a table that exists temporarily for the duration of a session or a transaction and is automatically dropped when the session ends or the transaction is committed or rolled back. Temporary tables are useful for storing intermediate results within a session or transaction.

There are two types of temporary tables in SQL

1. Local Temporary Tables

These are created using a single hash (#) before the table name, and they are only visible to the current session. Local temporary tables are automatically dropped when the session that created them ends or when explicitly dropped by the user.

ExampleCopy Code

CREATE TABLE #TempTable (
    ID INT,
    Name VARCHAR(50)
);

2. Global Temporary Tables

These are created using a double hash (##) before the table name, and they are visible to all sessions. Global temporary tables are dropped when all sessions referencing them are closed or when explicitly dropped by the user

ExampleCopy Code

CREATE TABLE ##TempTable (
    ID INT,
    Name VARCHAR(50)
);

Temporary tables can be used similarly to permanent tables for storing and manipulating data. However, they are typically used for temporary storage and are not intended for long-term data retention. Additionally, temporary tables are often used in scenarios where common table expressions (CTEs) or derived tables are not sufficient for the required operations.