title: Insert deleted rows to new table in stored procedure
body: <p>How can I <code>insert</code> the rows deleted in a <code>delete</code> statement into a new table within a stored procedure in DB2 SQL?</p>
  
  <p>DB2 allows the following syntax to return the deleted rows:</p>
  
  <pre><code>select * from old table (
      delete from my_table where foo &gt; 1
  )
  </code></pre>
  
  <p>For some reason, you can't just do an <code>insert</code> based on  the results returned from that statement.  <a href="https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/select_from_insert?lang=en" rel="nofollow">However, you can use common table expressions as a kludgy workaround</a>:</p>
  
  <pre><code>with deleted as (
      select * from old table (delete from my_table where foo &gt; 1)
  )
  select * from new table (insert into archive_table select * from deleted)
  </code></pre>
  
  <p>This has an unnecessary extra select statement that I don't want, but at least it works.  Deleted rows get inserted into another table.</p>
  
  <p><em>However, how can I do this within a stored procedure?</em>  </p>
  
  <p>A stored procedure doesn't allow a bare select statement.  I thought of putting it within a <code>set</code> statement:</p>
  
  <pre><code>set my_var = (
      with deleted as (
          select * from old table (delete from my_table where foo &gt; 1)
      )
      select * from new table (insert into archive_table select * from deleted)
  );
  </code></pre>
  
  <p>However, this fails because a common table expression is not allowed within such a statement.</p>
  
  <p><strong>Is there any way to do this within a stored procedure?</strong></p>
  
  <p><em>(The task can be done using some other method as a work-around.  But I want to find out if it is possible to do it this way.  If this is not possible, it seems like quite a dumb restriction.)</em>  </p>
  
  <p><strong>Update:</strong> I'm using DB2 9.7 LUW.</p>