Two ways to create temporary tables in SQL

In SQL, there are several ways to create temporary tables, depending on the functionality you want to implement and the type of database. Here are two common methods:

1. Use the WITH clause (commonly used in CTE, common table expression)

If you don't need to reuse temporary tables in multiple queries and just want to use intermediate results in one query, you can useWITHclause. This approach does not really create a physical temporary table, but generates a temporary result set.

WITH temp AS (
    SELECT column1, column2
    FROM original_table
    WHERE conditions
)
SELECT *
FROM temp
WHERE other_conditions;

2. Use CREATE TEMPORARY TABLE

If you need to create a temporary table that can be used throughout the session, you can useCREATE TEMPORARY TABLESentence. This creates a physically temporary table stored in the memory of the session or connected, usually automatically deleted at the end of the session.

CREATE TEMPORARY TABLE temp_table AS
SELECT column1, column2
FROM original_table
WHERE conditions;

-- It can then be used at any time in the session temp_table
SELECT *
FROM temp_table
WHERE other_conditions;

When to use the WITH clause vs. CREATE TEMPORARY TABLE

WITHClause: Suitable for intermediate result sets in a single query, especially if you don't need to reuse temporary result sets. It is usually more concise and has a lower performance overhead.
CREATE TEMPORARY TABLE: Suitable for situations where you need to use the same dataset in multiple queries or the entire session and need persistence beyond a single query.

Depending on your needs, choose the right method to create temporary tables.

Query temporary tables

select * from #Temporary table name;select * from ##Temporary table name;

Delete temporary tables

drop table #Temporary table name;drop table ##Temporary table name;

Instructions for use

drop table #Tmp --Delete temporary table#Tmpcreate table #Tmp --Create temporary table #Tmp(
    ID   int IDENTITY (1,1)     not null, --Create a columnID,And every time a new record is added1
    WokNo                varchar(50),   
    primary key (ID)      --definitionIDFor temporary table#Tmp's primary key);
Select * from #Tmp --Query the data of temporary tablestruncate table #Tmp --Clear all data and constraints of temporary tables

example

IF object_id('tempdb..#jimmy') is not NULL BEGIN
DROP TABLE #jimmy;
END
SELECT * 
INTO #jimmy
FROM table
WHERE 1=1

This is the end of this article about two methods of creating temporary tables in SQL. For more related content on creating temporary tables in SQL, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!