Advantage SQL Engine
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.
// 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