Create Temp Table in SQL Server Stored Procedure

In this SQL Server tutorial, I will show you how to create temp table in SQL Server stored procedure.

First, I will explain a temp table and its use in stored procedures. Then, with the example, you will understand how to use the CREATE TABLE command in the stored procedure to create a temp table.

Also, how to insert the data into the temp table, and finally, how to use the temp table in stored procedure to perform some operations.

Table of Contents

Create Temp Table in SQL Server Stored Procedure

First, you must know what a temporary (temp) table in SQL Server is. The temp table is created at runtime to temporarily store the intermediate data while executing a SQL Script or stored procedure.

These tables don’t exist physically and are automatically deleted when the session ends. The user can also explicitly delete them. There are two kinds of temporary tables: local and global temp tables.

The local temp table is denoted by a single has symbol (#) and visible to the connection that creates them, and the global temp table is denoted by a double has symbol (##) and visible to all the connection

If you don’t know how to create a temporary table, follow this tutorial How to Create Temporary Table in SQL Server Management Studio?

But the question is, why use the temporary table in the stored procedure? There are several reasons: performance, organization, scope and cleanup, and debugging.

Now that you know about the temporary table and why you should use it in a stored procedure, let’s see how to create a temp table using the stored procedure.

I have a table of SalesTransactions in my database, shown below.

Create Temp Table in SQL Server Stored Procedure Table Tansactions

Using this table, you have to create a stored procedure CalculateTotalSaleIncludingTax. The task of this stored procedure is to take all the data of the SalesTransactions table and store it in the new table #SaleData, then compute the total sale amount, including the tax for each transaction.

Now look at the query below, which does the same task.

CREATE PROCEDURE CalculateTotalSaleIncludingTax
AS
BEGIN
    -- Create a temporary table to store sale data
    CREATE TABLE #SaleData (
        TransactionID INT,
        SaleAmount MONEY,
        TaxRate DECIMAL(5, 2)
    );

    -- Insert data into the temporary table
    INSERT INTO #SaleData (TransactionID, SaleAmount, TaxRate) 
	SELECT TransactionID, SaleAmount, TaxRate FROM Sales.SalesTransactions;

    -- Calculate total sale amount including tax for each transaction
    SELECT 
        TransactionID, 
        SaleAmount, 
        TaxRate, 
        SaleAmount + (SaleAmount * (TaxRate / 100.0)) AS TotalSaleIncludingTax
    FROM #SaleData
    ORDER BY TransactionID;
END

Create Temp Table in SQL Server Stored Procedure

After executing the above command, a stored procedure, CalculateTotalSaleIncludingTax, is created. This procedure computes each transaction’s total sale amount, including tax.

Let us understand each statement with stored procedure.

CREATE TABLE #SaleData (
        TransactionID INT,
        SaleAmount MONEY,
        TaxRate DECIMAL(5, 2)
    );

This command creates a temp table named #SaleData with columns TansactionID, SaleAmount and TaxRate. This is where you must focus on defining the temp table in the stored procedure.

Next statement,

INSERT INTO #SaleData (TransactionID, SaleAmount, TaxRate) 
	SELECT TransactionID, SaleAmount, TaxRate FROM Sales.SalesTransactions;

This statement takes all the records from the SalesTransactions table and inserts them into temp table #SaleData.

The final statement,

SELECT 
        TransactionID, 
        SaleAmount, 
        TaxRate, 
        SaleAmount + (SaleAmount * (TaxRate / 100.0)) AS TotalSaleIncludingTax
    FROM #SaleData
    ORDER BY TransactionID;

This statement computes the total sale, including the tax for each transaction record. Here, the select command is called on the temp table #SaleData.

Now you know how the CalculateTotalSaleIncludingTax stored procedure works, execute this stored procedure.

EXEC CalculateTotalSaleIncludingTax;

Executing Stored Procedure to Create Temp Table

The stored procedure returns the total sale amount, including the tax for each transaction ID.

Here, you have to focus on the part where the temporary table is created, data is inserted into that table with a stored procedure, and how to use the temporary table to perform some operations.

This is how you can create temp table in SQL Server stored procedure.

Conclusion

In this SQL Server tutorial, you learned how to create temp table in SQL Servers stored procedure.

Additionally, you learned how to perform some operations on the temp table using a stored procedure and retrieve the required information, such as total sales, including tax.