Basically what I am trying to do in sql is find a way to do a select insert statement where all of the values in the other columns will stay the same but one of the columns value will increase by 1 for every row that is created. I am wondering if there is a way to do that in SQL.
>Solution :
You can use:
INSERT INTO table_name (col1, col2, col3)
SELECT col1, col2, col3 + 1
FROM table_name
Which, for the sample data:
CREATE TABLE table_name (col1, col2, col3) AS
SELECT LEVEL, LEVEL, LEVEL FROM DUAL CONNECT BY LEVEL <= 3;
Then, after the INSERT, the table contains:
COL1 COL2 COL3 1 1 1 2 2 2 3 3 3 1 1 2 2 2 3 3 3 4
And the col1 and col2 values of the inserted rows are the same and the col3 values have been incremented by 1.
If you want to increment the values by the number of rows being inserted then you can use:
INSERT INTO table_name (col1, col2, col3)
SELECT col1, col2, col3 + COUNT(*) OVER ()
FROM table_name
However
If you are attempting to add multiple rows and keep col3 with unique values then you should not use that method and should use a sequence.
If you have the table:
CREATE TABLE table_name (col1, col2, col3) AS
SELECT LEVEL, LEVEL, table_name__col3__seq.NEXTVAL FROM DUAL CONNECT BY LEVEL <= 3;
Then you can insert the rows using:
INSERT INTO table_name (col1, col2, col3)
SELECT col1, col2, table_name__col3__seq.NEXTVAL
FROM table_name
and col3 will be populated using the next sequence values.
db<>fiddle here