Generating incremental numeric column values during INSERT SELECT statement
- by Charles
I need to copy some data from one table to another in Oracle, while generating incremental values for a numeric column in the new table. This is a once-only exercise 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'm doing it with a temporary sequence, like so:
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) o;
DROP SEQUENCE temp_seq;
Is there way to do with without creating the sequence or any other temporary object? Specifically, can this be done with a self-contained INSERT SELECT statement?
There are similar questions, but I believe the specifics of my question are original to SO.
