Temporary Tables

THE SQL can be made up of multiple statements. For example temporary table(s) can be created and populated with INSERT and UPDATE statements. These can use sub queries and accept user parameter values entered by users. The final SELECT statement is the one which will actually be used to generate the report. This final SELECT statement can not have sub queries. Typically the steps would be :-

  1. Create one or more temporary tables as required
  2. Populate with these with UPDATE and INSERT using user parameters as required (<Condition> tags)
  3. Include a final SELECT statement which uses the temporary tables linked to the temporary tables. This can include user parameters (<Condition> tags) in its own right.
  4. Use DROP TABLE to drop the temporary tables (See DROP TABLE below if <Orderby> tag is used)
  5. Add <Orderby> tags as required

The DROP tables only applies to the OrderBy tag, which while still supported should not be used in future reports, because it will not work on large reports. Use  OutputTableOrderby instead.


Temporary tables can be created within XML reports and have a variety of uses.

Placing within the XML Report

Temporary tables are created as part of the SQL statements. In the standard construction for a report the SQL Select and From clauses are put outside the <Where> tags and the appropriate <Where> tag is evaluated and applied when the report is executed. When temporary tables are used the creating and populating Temporary tables and the final SQL statement special considerations apply. These are covered in detail in Where tag (Advanced).


Only temporary tables can be created. These are identified with a # suffix and this must be included in the table name. The following example illustrates how this is normally used and includes examples of the data types normally required. It is best to always specify the Collate statement (COLLATE SQL_Latin1_General_CP1_CI_AS ) when creating CHAR, VARCHAR and TEXT data types.  The PRIMARY KEY statement is optional, but may improve performance, When naming the Tables keep the names short. It is best not to inlcude spaces in the tables or field names. Also avoid SQL reserved words.     

CREATE TABLE #Locations3 (Location_Key CHAR(16) COLLATE SQL_Latin1_General_CP1_CI_AS PRIMARY KEY, Item_Name VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS ,Preferred Bit,Validation INTEGER, InpuTDate DATETIME, Description TEXT COLLATE SQL_Latin1_General_CP1_CI_AS)

SQL for Populating Temporary Table

The INSERT command is used to populate the temporary table. There are a number of different ways of constructing these. The following is appropriate in most circumstances.The select element can include WHERE clauses derived from <Condition> tags.

INSERT INTO #Locations (Location_Key,Item_Name,Preferred)
SELECT Location_Key,  Item_Name,Preferred FROM Location_Name

 Once there are records in the table they can be updated. using the UPDATE statement.

Create Table #L (location_key char(16) COLLATE SQL_Latin1_General_CP1_CI_AS,
Custodian  varchar(8
) COLLATE SQL_Latin1_General_CP1_CI_AS)
INSERT INTO #L (Location_key) Select location_key from Location
UPDATE  #L set Custodian  =  Location.Custodian
WHERE  Location.Location_Key  = #L.Location_Key


Create Table #L (location_key char(16) COLLATE SQL_Latin1_General_CP1_CI_AS,
Custodian  varchar(8) COLLATE SQL_Latin1_General_CP1_CI_AS)
INSERT INTO #L (Location_key) Select location_key from Location
UPDATE  #L set Custodian  =  Location.Custodian
INNER JOIN  #L ON  #L.Location_Key = Location_Key

It is also possible to use WHILE/WEND loops. These are useful for expanding hierarchies. The following SQL is used  for expanding a selected Location to include its key and the keys for all Locations below it in the hierarchy.

CREATE TABLE #Locations (Location_Key CHAR(16) COLLATE SQL_Latin1_General_CP1_CI_AS PRIMARY KEY,
      Parent_Key CHAR(16)  COLLATE Database_Default)

INSERT INTO #Locations (LOcation_Key,Parent_Key)
<Condition field= "L.Location_key" operator="equal" type="Location"  name="Select Locations" entrycount="-1" />
 INSERT INTO #Locations
  SELECT L.Location_Key, Tinc.Parent_Key
  FROM Location L
  INNER JOIN #Locations Tinc on L.Parent_Key=Tinc.Location_Key
  LEFT JOIN #Locations Texc ON Texc.Location_Key=L.Location_Key
  WHERE Texc.Location_Key IS NULL


The temporary table needs to be dropped after final use. If it is not then the report may not open for a second time until Recorder is closed and re-opened. If you have not used the <Orderby> attribute then the  DROP TABLE statement (eg. DROP TABLE #MainResults) can be placed immediately before the closing </where > attribute. If the <Orderby> attribute has been used then DROP TABLE needs to be incorporated into the SQL of the <Orderby> attribute, (eg.  
<Orderby name="by Taxon Group/preferred_Name" SQL="Order By tg.taxon_group_name, itn.preferred_name DROP TABLE #MainResults "/> )

Note that this is not the case with the <OutputTableOrderBy. DROP TABLE should be included at the end of SQL.    < /FONT>

Using in Quick Reports

The %s parameter returned when running as a Quick report can only be used once. If it is required for use in more than one place (for example in several update statements) then it needs to be held in a temporary table or as a variable.

Further Examples

There are a number of examples of creating temporary tables in Where tag (Advanced). and also in SQL Queries - How To



Created by Atop CHM to web converter,© 2009 all right reserved.