SQL 2005+

SQL Server Temporary Tables

by Richard Carr, published at http://www.blackwasp.co.uk/SQLTemporaryTables.aspx

The fifty-seventh part of the SQL Server Programming Fundamentals tutorial investigates temporary tables. Temporary tables can be used to hold information during long-running or complex operations without locking live data rows.

Populating the Contract Value and Costs

Inside the loop we need to retrieve the monetary values for the contract value, engineer cost and parts cost. Each of these will be held in a variable that will be used in a subsequent UPDATE statement to apply the values to the appropriate columns in the temporary table. Note that the UPDATE statement checks to see if any of the values is null. If any are, they are replaced with zeroes.

SELECT @TotalContractValue = sum(ContractValue) FROM Contracts C
INNER JOIN CustomerAddresses A ON C.CustomerAddressId = A.AddressId
WHERE A.CustomerNumber = @CustomerNumber

SELECT @EngineerCost = sum(J.Duration * E. HourlyRate) FROM Jobs J
INNER JOIN Engineers E ON J.EngineerId = E.EngineerId
INNER JOIN Contracts C ON J.ContractNumber = C.ContractNumber
INNER JOIN CustomerAddresses A ON C.CustomerAddressId = A.AddressId
WHERE A.CustomerNumber = @CustomerNumber

SELECT @PartsCost = sum(TotalCost) FROM UsedParts P
INNER JOIN Jobs J ON P.JobId = J.JobId
INNER JOIN Contracts C ON J.ContractNumber = C.ContractNumber
INNER JOIN CustomerAddresses A ON C.CustomerAddressId = A.AddressId
WHERE A.CustomerNumber = @CustomerNumber

UPDATE
    #CustomerValue
SET
    TotalContractValue = isnull(@TotalContractValue, 0),
    EngineerCost = isnull(@EngineerCost, 0),
    PartsCost = isnull(@PartsCost, 0)
WHERE
    CustomerNumber = @CustomerNumber

We can now end the loop and close and deallocate the cursor:

    FETCH NEXT FROM CustomerCursor
    INTO @CustomerNumber
END

CLOSE CustomerCursor
DEALLOCATE CustomerCursor

Calculating the Profits

The cursor completed all of the column data except the Profit column. This can be calculated from the costs and the contract value after the loop has concluded. Once calculated, a query is executed to return the data from the temporary table:

UPDATE #CustomerValue
SET Profit = TotalContractValue - EngineerCost - PartsCost

SELECT * FROM #CustomerValue ORDER BY CustomerNumber

Dropping the Temporary Table

Although a temporary table will be dropped automatically once the connection using it is closed, it is advisable to remove it manually when it is no longer required. As our stored procedure has completed processing the data, the table may be dropped using the same syntax as for a standard table:

DROP TABLE #CustomerValue

This completes the stored procedure. To commit it to the database, execute the script. You can now run the procedure to see the information that it collects and returns.

Creating a Global Temporary Table

The stored procedure made use of a local temporary table, which is visible only to the connect that created it. As mentioned earlier, you can create a temporary table that is visible to all users of a database. To do so, the table name must be prefixed with two hash (or pound) characters.

Try creating the following temporary table and accessing it via two separate connections to the database:

CREATE TABLE ##CustomerValue
(
    CustomerNumber INT,
    TotalContractValue MONEY,
    EngineerCost MONEY,
    PartsCost MONEY,
    Profit MONEY
)