Generating incremental numeric column values ​​during an INSERT SELECT statement

Question

I need to copy some data from one table to another in Oracle and generate incremental values ​​for a numeric column in a new table. This exercise is once with a trivial number of rows (100).

I have an adequate solution to this problem, but I'm curious to know if there is a more elegant way.

I do this with a time sequence, for example:

CREATE SEQUENCE temp_seq START WITH 1; INSERT INTO new_table (new_col, copied_col1, copied_col2) SELECT temp_seq.NEXTVAL, o.* FROM (SELECT old_col1, old_col2 FROM old_table, ORDER BY old_col1) o; DROP SEQUENCE temp_seq; 

Is there a way to do this without creating a sequence or any other temporary object? In particular, can this be done using the stand-alone INSERT SELECT?

Note that a trigger is not an option.

FURTHER INFORMATION: I would like to control the order in which new rows are inserted, and this will not be the order they were created in the old table (note that I added ORDER BY above). But I still want my new sequential column to start with 1.

There are similar questions, but I believe that the specifics of my question are original for SO.

Answer 1

You can use ROWNUM . This pseudo-column displays the rows in your result:

 Insert Into new_table (new_col, copied_col1, copied_col2) Select Rownum, old_col1, old_col2 From old_table; 

If you want your records to be sorted, you need to use a subquery:

 Insert Into new_table (new_col, copied_col1, copied_col2) Select Rownum, old_col1, old_col2 From ( Select old_col1, old_col2 From old_table Order By old_col1 ); 

Answer 2

Why don't you define the new_col column as primary_key or unique and mark it as auto increment? Thus, each insert will receive the next higher “score”.

I am not very familiar with the oracle, but I would argue that there is a built-in auto-increment function.