title: How can I select data from two tables using an inner join and insert directly into a third table?
body: <p>I have two tables with schemas as shown below:</p>
  <pre><code>user.table1
  pid       =&gt; varchar
  timestamp =&gt; timestamp
  sid       =&gt; varchar
  
  pid  timestamp    sid  attribute1  attribute2  ...
  -------------------------------------------------------------------
  1    2020/01/20   10   ...         ...         ...
  2    2020/02/28   10   ...         ...         ...
  3    2020/03/01   10   ...         ...         ...
  4    2020/04/08   20   ...         ...         ...
  5    2020/05/31   20   ...         ...         ...
  6    2020/06/30   20   ...         ...         ...
  7    2020/06/31   20   ...         ...         ...
  8    2020/07/31   20   ...         ...         ...
  </code></pre>
  <pre><code>user.table2
  pid  =&gt; varchar
  text =&gt; blob
  
  pid  text
  -------------------------------------------------------------------
  1    xxx
  2    abc
  3    def
  4    yyy
  5    sss
  6    abc
  7    rrr
  8    fff
  </code></pre>
  <p>I need to create a third table that should contain the information shown below:</p>
  <pre><code>user.table3
  pid  timestamp    sid  text
  -------------------------------
  1    2020/01/20   10   xxx     
  2    2020/02/28   10   abc     
  3    2020/03/01   10   def     
  4    2020/04/08   20   yyy     
  5    2020/05/31   20   sss     
  6    2020/06/30   20   abc     
  7    2020/06/31   20   rrr     
  8    2020/07/31   20   fff     
  </code></pre>
  <p>Any idea how to do the select, inner join, and the insert in one SQL statement?</p>
  <p>The reason I want to do it in one SQL statement is that I don't want to pull the information from the DB into Java and then write it back to the DB. I did the latter earlier, but that is running very slowly.</p>
  <p>Currently, even the select I wrote below is not working.</p>
  <pre><code>select 
    table1.pid, table1.sid, table1.timestamp, utl_raw.cast_to_varchar2(dbms_lob.substr(table2.text,10,1)) 
  from user.table1 inner join user.table2 
  using pid 
  where pid in ('1', '2');
  </code></pre>