Temporary Table in SQL Server

Temporary Table in SQL Server

1. If you need to store a large amount of data to a temporary location then it is a good idea to store this in a temporary table rather than on a table variable.
2. A temporary table is created in the "tempdb" of the SQL Server. This is a separate database. So, this is an additional overhead and can causes performance issues.
3. The number of rows and columns needs to be as small as needed.
4. Tables need to be deleted when they are done with their work.
5. In SQL Server, all temporary tables are prefixed with"#".

When to Use Temporary Tables?

1. This is useful to replace the cursor.
2. When we are having a complex join operation.
3. When we are doing a large number of row manipulations in Stored Procedures.

Example 1

CREATE TABLE #Temp_Table
(
       id INTEGER,
       name VARCHAR(100)
);

INSERT INTO #Temp_Table VALUES(1, 'MAHESH')
 
SELECT * FROM #Temp_Table
 
DROP TABLE #Temp_Table

Example 2

CREATE TABLE #Temp_Table
(
       Customer_Id INTEGER,
       Customer_Name VARCHAR(100)
);
 
INSERT INTO #Temp_Table
SELECT Customer_Id, Customer_Name
FROM [dbo].[MST_Customer]
WHERE Customer_Name LIKE 'Micr%'
 
SELECT * FROM #Temp_Table
 
DROP TABLE #Temp_Table

Example 3

Here I am using a Stored Procedure to insert values into a temporary table.

CREATE TABLE #Temp_Table
(
       Customer_Id INTEGER,
       Customer_Name VARCHAR(100),
       [password] varchar(100)
);
 
INSERT INTO #Temp_Table
EXEC [USP_Login] 'mahesh', 'mahesh' --[USP_Login] is my store procedure.
 
SELECT * FROM #Temp_Table
 
DROP TABLE #Temp_Table

Example 4

Here the temporary table will be automatically created. I am not creating a temporary table using a create statement.

SELECT Customer_Id, Customer_Name
INTO #Temp_Table
FROM [dbo].[MST_Customer]
WHERE Customer_Name LIKE 'Micr%'
 
SELECT * FROM #Temp_Table
 
DROP TABLE #Temp_Table

Example 5

Here I am using two temporary tables and fetching records form there two temporary tables.

SELECT customer_id, veh_regn_no
INTO #FDetails
FROM [dbo].[Fuelling_Details]
 
SELECT Customer_Id, Customer_Name
INTO #MSTC
FROM [dbo].[MST_Customer]
 
 
SELECT DISTINCT A.customer_id, Customer_Name, veh_regn_no
FROM #FDetails A INNER JOIN #MSTC B
ON A.customer_id = B.Customer_Id
 
DROP TABLE #FDetails, #MSTC