How can you create and use temporary tables?
PaulAnd
Posted by PaulAnd
Last Updated: June 27, 2024
temporary tables
Temporary tables are a useful feature in database management systems (DBMS) that allow you to store data temporarily while performing operations. They exist only for the duration of the database session or until they are explicitly dropped. Here’s how to create and use temporary tables in a few popular SQL databases:
Creating Temporary Tables
SQL Server
To create a temporary table in SQL Server, you use the CREATE TABLE statement with a # prefix. 
-- Create a temporary table
CREATE TABLE #TempTable (
    ID INT,
    Name NVARCHAR(50)
);
MySQL
In MySQL, you can create a temporary table using the CREATE TEMPORARY TABLE statement. 
-- Create a temporary table
CREATE TEMPORARY TABLE TempTable (
    ID INT,
    Name VARCHAR(50)
);
PostgreSQL
In PostgreSQL, the CREATE TEMP TABLE statement is used to create temporary tables. 
-- Create a temporary table
CREATE TEMP TABLE TempTable (
    ID SERIAL PRIMARY KEY,
    Name VARCHAR(50)
);
Inserting Data into Temporary Tables
Once you have created the temporary table, you can insert data in the same way as you would with a regular table. 
-- Insert data into the temporary table
INSERT INTO #TempTable (ID, Name) VALUES (1, 'John Doe');
Querying Temporary Tables
You can query a temporary table like any other table. 
-- Selecting data from the temporary table
SELECT * FROM #TempTable;
Updating Data in Temporary Tables
You can update the data in the temporary table as needed. 
-- Update data in the temporary table
UPDATE #TempTable SET Name = 'Jane Doe' WHERE ID = 1;
Dropping Temporary Tables
Temporary tables are automatically dropped at the end of the session, but you can also drop them explicitly if you wish. 
-- Drop the temporary table
DROP TABLE #TempTable;
Example Usage
Here’s a simple example of how you might use a temporary table: 
-- Create a temporary table
CREATE TEMPORARY TABLE TempSales (
    SaleID INT,
    Amount DECIMAL(10, 2)
);

-- Insert sample sales data
INSERT INTO TempSales (SaleID, Amount) VALUES (1, 100.00), (2, 200.00), (3, 150.50);

-- Query to calculate total sales
SELECT SUM(Amount) AS TotalSales FROM TempSales;

-- The temporary table will be automatically dropped when the session is finished.
Important Notes
1. Scope: Temporary tables are session-specific. In SQL Server, a single # symbol makes the table private to your session, whereas ## makes it global across all sessions. 2. Performance: While temporary tables can improve performance by allowing you to break down complex queries, they can also take up system resources. 3. Transaction Handling: Temporary tables respect transactions, so changes made to them can be rolled back. By utilizing temporary tables effectively, you can simplify data processing and improve the efficiency of your SQL queries.
Default Image