Temp table | SQL Tutorial and Query Example

Text copied!

Temp table

July 1, 2023, 6:18 a.m. under SQL
    • Session :

    When a user logs in and executes SQL statement to access SQL Server database engine, a connection is established. This connection is referred to as a 'session'. When a user closes the current query tab or logs out from the SQL Server database engine, the current session is ended.

    There are two types of Temporary tables :
    [a] Local temporary table :

    Local temporary tables are only visible to the session that created them and are dropped automatically when the session ends. To create it, place single hash (#) before the table name.

    [b] Global temporary table :

    Global temporary tables are accessible to all sessions, but they are dropped automatically when the session that created them ends. To create it, place double hash (##) before the table name.

    Here's an example of Local temp table :

    [i] Let's assume you want to create a table that stores data temporarily and gets deleted automatically when you close the current query tab or logs out from the SQL Server database engine. It should be accessible within current query tab or session only.

    CREATE TABLE #TempTableName (
    Column1 data_type,
    Column2 data_type,
    ...
);
    We know that 'Local temporary tables' are accessible exclusively within the current query tab or session.

    [vi] Let's demonstrate this, run the below statement in a different or newer query tab.

    Remember :

    This '#LocalTemp_Employees' temporary table will be automatically deleted when the user closes the current query tab or session. This '##GlobalTemp_Employees' temporary table will be automatically deleted when the user closes the current query tab or session.

    CREATE TABLE ##GlobalTableName (
    Column1 data_type,
    Column2 data_type,
    ...
);
    Here's an example of Global temp table :

    [i] Let's assume you want to create a table that stores data temporarily and gets deleted automatically when you close the current query tab or logs out from the SQL Server database engine. It should be accessible to all the query tabs or session.

    We know that 'Global temporary tables' are accessible to all the query tabs or session.

    [vi] Let's demonstrate this, run the below statement in a different or newer query tab.

    CREATE TABLE #LocalTemp_Employees (
	Employee_Id INT,
	Employee_Name Varchar(100),
	Gender Varchar(10),
	Salary INT
);
    Temp table 
    SELECT * 
FROM #LocalTemp_Employees;
    Temp table 
    SELECT * 
FROM #LocalTemp_Employees;
    Temp table 
    CREATE TABLE ##GlobalTemp_Employees (
	Employee_Id INT,
	Employee_Name Varchar(100),
	Gender Varchar(10),
	Salary INT
);
    Temp table 
    SELECT * 
FROM ##GlobalTemp_Employees;
    Temp table 
    SELECT * 
FROM ##GlobalTemp_Employees;
    Temp table

    Frequently Asked Questions :

    A temp table in SQL is a temporary table that is created and exists only for the duration of a database session.
    In SQL, there can be multiple temp tables created within a session, but they are temporary and scoped to the session or transaction.
    The main difference between a temp table and a view in SQL is that a temp table holds data temporarily during a session, while a view is a virtual table generated dynamically from a SELECT query.
    Common Table Expressions (CTEs) are preferred over temp tables when the result set is needed only for a single query because CTEs provide better readability and maintainability without the need for creating physical tables in the database.
    « Previous
    Next »
    RELATED :