coldfusion-8

Creating temp tables in ColdFusion (copying records without duplicate id)

I know, I know, this is older than your grandma but I can't figure out for the life of me why I can execute the following query on a phpmyadmin but not in a cfquery tag.

  DROP TABLE IF EXISTS temp;
  CREATE TEMPORARY TABLE temp ENGINE=MEMORY
Solution

  • I realized I can't make more than one query per cfquery tag. Essentially this is for copying records in MySQL with ColdFusion. If someone can simplify this further I'd appreciate it.

      <cfquery datasource="#request.datasource#">
      DROP TABLE IF EXISTS temp
  </cfquery>
  <cfquery result="copied" datasource="#request.datasource#">
      CREATE TABLE temp ENGINE=MEMORY
      SELECT * FROM mytable WHERE id= <cfqueryparam value="#id_im_looking_for#">
  </cfquery>

  <cfquery result="copied" datasource="#request.datasource#">
    INSERT INTO mytable SELECT NULL, name, description, NOW() FROM temp
  </cfquery>

  <cfquery datasource="#request.datasource#">
    DROP TABLE temp
  </cfquery>

    A little verbose to me.