Navigation:  Advantage SQL > SQL Functionality >

Using Temporary Tables in SQL Statements

Advantage SQL Engine

Previous pageReturn to chapter overviewNext page

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