Using Temporary Tables in SQL Statements

Found this to be a really helpful tip from Using Temporary Tables in SQL Statements. I wanted to be able to quickly construct a temporary table within a stored procedure and this was just a super-simple set of examples to get that going.

Hope others find it useful as well!

Temporary tables can be used in the SQL statement wherever the regular tables can be used. To identify a table being a temporary table in the SQL statement, prefix the table name with the ‘#’ character.

Examples:

// Create a temporary table named Temp1 with two columns
CREATE TABLE #Temp1 ( Name Char( 30 ), seqid integer );

// This example creates two temporary tables for intermediate results
// Step 1. Create a temporary table named DeptCount and at the same time
// populate it with summary data from an existing table in the
// database

SELECT deptnum, count(*) as NumEmployees
INTO #DeptCount
FROM employees
GROUP BY deptnum

// Step 2. Create another temporary table named LocCount which list the
// number of employees in each location for each department.

SELECT deptnum, location, count(*) as cnt
INTO #LocCount
FROM employees
GROUP BY deptnum, location

// Finally using the 2 temporary tables to list the percent of employee
// on each location for each department

SELECT a.deptnum, a.location, ( a.cnt * 100 ) / b.NumEmployees As PercentAtLocation
FROM #LocCount a, #DeptCount b
WHERE a.deptnum = b.deptnum