title: MySQL temporary tables do not clear
body: <p>Background - I have a DB created from a single large flat file. Instead of creating a single large table with 106 columns. I created a "columns" table which stores the column names and the id of the table that holds that data, plus 106 other tables to store the data for each column. Since not all the records have data in all columns, I thought this might be a more efficient way to load the data (maybe a bad idea). </p>
  
  <p>The difficulty with this was rebuilding a single record from this structure. To facilitate this I created the following procedure:</p>
  
  <pre><code>DROP PROCEDURE IF EXISTS `col_val`;
  delimiter $$
  
  CREATE PROCEDURE `col_val`(IN id INT)
  BEGIN
      DROP TEMPORARY TABLE IF EXISTS tmp_record;
      CREATE TEMPORARY TABLE tmp_record (id INT(11), val varchar(100)) ENGINE=MEMORY;
      SET @ctr = 1;
      SET @valsql = '';
      WHILE (@ctr &lt; 107) DO
          SET @valsql = CONCAT('INSERT INTO tmp_record SELECT ',@ctr,', value FROM col',@ctr,' WHERE recordID = ',@id,';');
          PREPARE s1 FROM @valsql;
          EXECUTE s1;
          DEALLOCATE PREPARE s1;
          SET @ctr = @ctr+1;
          END WHILE;
  END$$
  DELIMITER ;
  </code></pre>
  
  <p>Then I use the following SQL where the stored procedure parameter is the id of the record I want.</p>
  
  <pre><code>CALL col_val(10);
  SELECT c.`name`, t.`val`
  FROM `columns` c INNER JOIN tmp_record t ON c.ID = t.id
  </code></pre>
  
  <p>Problem - The first time I run this it works great. However, each subsequent run returns the exact same record even though the parameter is changed. How does this persist even when the stored procedure should be dropping and re-creating the temp table?</p>
  
  <p>I might be re-thinking the whole design and going back to a single table, but the problem illustrates something I would like to understand.</p>
  
  <p>Unsure if it matters but I'm running MySQL 5.6 (64 bit) on Windows 7 and executing the SQL via MySQL Workbench v5.2.47 CE.</p>
  
  <p>Thanks,</p>