{"id":108,"date":"2010-05-20T20:00:59","date_gmt":"2010-05-21T00:00:59","guid":{"rendered":"http:\/\/erics-test-lab.com\/?p=108"},"modified":"2010-05-20T20:00:59","modified_gmt":"2010-05-21T00:00:59","slug":"temp-tables-and-bad-habits","status":"publish","type":"post","link":"https:\/\/www.esearing.com\/v2010\/2010\/05\/temp-tables-and-bad-habits\/","title":{"rendered":"Temp tables and bad habits"},"content":{"rendered":"

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.
\n<cfquery name=\"Q1\" datasource=\"myDsn\">
\n select *
\n into ##temp1<\/strong>
\n from mytable
\n<\/cfquery>
\n<\/code><\/p>\n

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. <\/p>\n

We have a couple of remedies:<\/p>\n

    \n
  1. Use unique meaningful names for temp tables like orders_tmp1<\/li>\n
  2. Use a dynamic variable with random numbers to make the string long and unique per session<\/li>\n
  3. Use a datetime stamp as part of the the temp table name.<\/li>\n<\/ol>\n

    Dynamic Variable Example:
    \n<cfset ord_tmp1 = \"ord_tmp1 & #randrange(1000,100000)# & #randrange(1000,100000)#\">
    \n\/\/ yields something like \"ord_tmp19876512345\"
    \n<cfquery name=\"Q1\" datasource=\"myDsn\">
    \n select *
    \n into ###ord_tmp1# <\/strong> <--notice the extra pound signs.\n from mytable\n<\/cfquery>
    \n<\/code><\/p>\n

    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.<\/p>\n","protected":false},"excerpt":{"rendered":"

    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 … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,16,14],"tags":[],"class_list":["post-108","post","type-post","status-publish","format-standard","hentry","category-code","category-coldfusion","category-sql"],"_links":{"self":[{"href":"https:\/\/www.esearing.com\/v2010\/wp-json\/wp\/v2\/posts\/108","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.esearing.com\/v2010\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.esearing.com\/v2010\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.esearing.com\/v2010\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.esearing.com\/v2010\/wp-json\/wp\/v2\/comments?post=108"}],"version-history":[{"count":0,"href":"https:\/\/www.esearing.com\/v2010\/wp-json\/wp\/v2\/posts\/108\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.esearing.com\/v2010\/wp-json\/wp\/v2\/media?parent=108"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.esearing.com\/v2010\/wp-json\/wp\/v2\/categories?post=108"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.esearing.com\/v2010\/wp-json\/wp\/v2\/tags?post=108"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}