title: Creating and using temporary/volatile database tables In Stata
body: <h2>Addendum: As of Stata 14, volatile tables work without any hacks.</h2>
  
  <p>Is there a way to tweak Stata to work with temporary volatile tables? These tables and the data are deleted after a user logs off the session.</p>
  
  <p>Here's an example of a simple <strong>toy</strong> SQL query that I am using in Stata and Teradata:</p>
  
  <pre><code>odbc load,  exec("
      BEGIN TRANSACTION;
      CREATE VOLATILE MULTISET TABLE vol_tab AS (
          SELECT TOP 10 user_id
          FROM dw_users
      ) WITH DATA
      PRIMARY INDEX(user_id)
      ON COMMIT PRESERVE ROWS;
  
      SELECT * FROM vol_tab;
      END TRANSACTION;
  ") dsn("mozart");
  </code></pre>
  
  <p>This is the error message I am getting:</p>
  
  <pre><code>The ODBC driver reported the following diagnostics
  [Teradata][ODBC Teradata Driver][Teradata Database] Only an ET or null statement is legal after a DDL Statement.
  SQLSTATE=25000
  r(682);
  </code></pre>
  
  <p>The Stata error code means:</p>
  
  <blockquote>
    <p>error . . . . . . . . . . . . . . . . . . . . . . . .  Return code
    682
            could not connect to odbc dsn;
            This typically occurs because of incorrect permissions, such
            as a bad User Name or Password.  Use set debug on to display
            the actual error message generated by the ODBC driver.</p>
  </blockquote>
  
  <p>As far as I can tell permission are fine since I can pull data if I just execute the "SELECT TOP 10..." query. I set debug on, but it did not produce any additional information. </p>
  
  <p>Session mode is Teradata. ODBC manager is set to unixODBC. I am using Stata 13.1 on an Ubuntu server.</p>
  
  <p>I believe the underlying issue may be that <a href="http://www.stata.com/manuals13/dodbc.pdf#page=4&amp;search=%22only%20one%20connection%22" rel="nofollow">separate connections are established for each SQL statement</a>, so the volatile table evaporates by the time the select is issued. I am waiting on tech support to verify this.</p>
  
  <p>I tried using the <code>odbc sqlfile</code> command well, but this approach does not work unless I create a permanent table at the end of it. There's no load option with <code>odbc sqlfile</code>. </p>
  
  <p>Volatile tables seem to work just fine in <a href="http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a002677096.htm" rel="nofollow">SAS</a> and R. For example, this works perfectly:</p>
  
  <pre><code>library("RODBC")
  db &lt;- odbcConnect("mozart")
  sqlQuery(db,"CREATE VOLATILE MULTISET TABLE vol_tab AS (
           SELECT TOP 10 user_id
           FROM dw_users
       ) WITH DATA
       PRIMARY INDEX(user_id)
       ON COMMIT PRESERVE ROWS;
  ")
  data&lt;- sqlQuery(db,"select * from vol_tab;",rows_at_time=1)
  </code></pre>
  
  <p>Perhaps this is because the connection to the DB remains open until <code>close(db)</code>.</p>