Generating incremental numeric column values during INSERT SELECT statement
You can use ROWNUM. This pseudo-column numbers 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 sub-query:
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 );
Why don't you define the column new_col as primary_key or unique and mark it as autoincrement?This way each insert will get the next higher "count".
I'm not pretty familiar with oracle, but i would bet there is a built in autoincrement function.