How to do a select insert statement in sql with a changing value in the column for each row-CodePudding

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.

CodePudding user response：

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

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