title: How to move a DB2 SQL result table into a physical file?
body: <p>I would like to get the result table from a <code>SELECT</code> statement, and then move it into a physical file.</p>
  
  <p>I was hoping to use (per <a href="http://www.w3schools.com/sql/sql_select_into.asp" rel="nofollow">W3Schools</a>):</p>
  
  <pre><code>SELECT column_name(s)
  INTO new_table_name [IN externaldatabase]
  FROM old_tablename 
  </code></pre>
  
  <p>Unfortunately per the IBM Manual (DB2 for i SQL reference)</p>
  
  <blockquote>
    <p>The <code>SELECT INTO</code> statement produces a result table consisting of at <strong>most one row</strong>, and assigns the values in that row to variables.</p>
  </blockquote>
  
  <p>So then I thought maybe I could use:</p>
  
  <blockquote>
    <p>The <code>INSERT</code> statement inserts rows into a table or view
        There are three forms of this statement:<br>
    1. The <code>INSERT</code> using <em>fullselect</em> form is used to insert one or more rows into the table or view using values from other tables or views.</p>
  </blockquote>
  
  <p>Unfortunately it does not seem to work for me.</p>
  
  <pre><code>INSERT INTO &lt;tablename&gt;
  FROM ( SELECT (*) FROM &lt;querytableA&gt;
         UNION
         SELECT (*) FROM &lt;querytableB&gt;)
  </code></pre>
  
  <p>I am able to move a result table into a physical file, but to do this I have to declare a cursor and then with a <code>do … while</code> write to the physical file, one record at a time. I consider this <strong>ugly and unelegant</strong> and was hoping to be able to use a <code>SELECT INTO</code> or a <code>INSERT INTO</code> statement.</p>
  
  <p>Please help me “dump” the result table into a physical file with one elegant statement.</p>
  
  <p>OS: IBM i v6.1<br>
  Database: DB2</p>