We often have need to hold the results of a query in a temporary table so we can join or perform other tasks in later queries.
<cfquery name="Q1" datasource="myDsn">
select *
into ##temp1
from mytable
</cfquery>
It is nice that SQL lets you create a temporary table to use in future queries. However, if we get lazy naming the temp tables we can run into some conflicts. Imagine if you name your temp tables like ##temp1 for the first query of every cfm in your application. If you happen to run more than one report or tool that uses the ##temp1 table you will likely be thrown errors in coldfusion telling you the table already exists.
We have a couple of remedies:
- Use unique meaningful names for temp tables like orders_tmp1
- Use a dynamic variable with random numbers to make the string long and unique per session
- Use a datetime stamp as part of the the temp table name.
Dynamic Variable Example:
<cfset ord_tmp1 = "ord_tmp1 & #randrange(1000,100000)# & #randrange(1000,100000)#">
// yields something like "ord_tmp19876512345"
<cfquery name="Q1" datasource="myDsn">
select *
into ###ord_tmp1# <--notice the extra pound signs.
from mytable
</cfquery>
Once you drop the browser your temp tables should be wiped out. However you can take the extra step of dropping the tables in a final query. Some languages like PHP or ASP may even require you to close open queries for security or at least a best practice.
