A SQL temp table, or SQL temporary table, is a special type of table in SQL that is used to store temporary data. SQL Temp tables are often used to store intermediate results for further processing or to store large amounts of data that are being imported or exported.
SQL Temp tables are created using the CREATE TABLE statement, just like regular tables, but they are created in the tempdb database and are prefixed with the #symbol. For example,
-- Create a temp table
CREATE TABLE #TempTable (
Id int,
Name varchar(255)
);
-- Insert data into the temp table
INSERT INTO #TempTable (Id, Name)
VALUES (1, 'Uday'), (2, 'Jay'), (3, 'Bhavesh');
-- Select data from the temp table
SELECT * FROM #TempTable;
-- Update data in the temp table
UPDATE #TempTable
SET Name = 'Jaya'
WHERE Id = 2;
-- Delete data from the temp table
DELETE FROM #TempTable
WHERE Id = 1;
-- Drop the temp table
DROP TABLE #TempTable;
This code creates a SQL temp table called #TempTable with two columns, Id and Name, inserts three rows of data into the table, selects all the data from the table, updates the Name of the second row, deletes the first row, and finally drops the temp table.
SQL Temp tables are automatically deleted when the session that created them ends, or when the script that created them finishes running. They are only visible to the connection that created them and is not visible to other connections.
There are several benefits to using temp tables in SQL,
- They allow you to store and manipulate large amounts of data without affecting the performance of your permanent tables.
- They allow you to perform complex calculations and manipulations on data without modifying the original data.
- They can be used to pass data between stored procedures and functions.
- They allow you to create and manipulate data in a temporary space, which can be useful when working with sensitive data.
There are also some considerations to keep in mind when using temp tables,
- They are only visible to the connection that created them, so they cannot be shared between connections.
- They are automatically deleted when the session ends, so you need to be careful not to rely on the data in a temp table for long-term storage.
- They can consume a lot of space in the tempdb database if they are not properly managed, so it is important to drop or delete temp tables when they are no longer needed.