How to insert columns with adding sequence column?
<p>In an Oracle database (11gR2), I have a table <code>my_table</code> with columns (<em>sequence</em>, <em>col1</em>, <em>col2</em>, <em>col3</em>). I want to insert values into the table that are queried from other tables, i.e. <code>insert into my_table select &lt;query from other tables&gt;</code>. The problem is that the primary key is the four columns, hence I need to add a sequence starting from 0 up till the count of the rows to be inserted (order is not a problem).</p>

<p>I tried using a loop like this:</p>

<pre><code>DECLARE
    j    NUMBER;
    r_count number;
BEGIN
    select count(1) into r_count from &lt;my query to be inserted&gt;;
    FOR j IN 0 .. r_count
    LOOP
        INSERT INTO   my_table
              select &lt;my query, incorporating r_count as sequence column&gt; ;
    END LOOP;
END;
</code></pre>

<p>But it didn't work, actually looped <code>r_count</code> times trying to insert the entire rows every time, as logically it shall do. How can I achieve the expected goal and insert rows with adding a sequence column?</p>