In SQL, how to populate number in sequence in column without alter the table structure?

Question

populating number in a sequence for a column without altering table structure--need a query

You need to try to do it yourself, show some of your work, and be specific about where you are stuck. SO isn't a free code writing service. See stackoverflow.com/help/how-to-ask — Aaron Dietz

Eralper Eralper · Accepted Answer · 2018-08-06T07:18:47

if you don't want to change or alter the database table structure, you have limited solutions actually.

First option might be using a sequence object

create sequence mySequence increment by 1;

After you have a sequence object, you can read from this sequence whenever you need this field value

select mySequence.nextval from dummy;

Unfortunately, each read will increment the current value of the sequence. This means if you read but did not insert the value into the table, next time you insert a new row there will be a gap in your table column. Or if you insert two rows from two different executions, one might read the sequence first but insert value later than the other thread; then the sequence will be like 11,12,14,13,15

Second option is creating a database table trigger AFTER INSERT statement. Then you can the max value from table column (or can store in a separate table) or from sequence, and update the column field with this sequence data

In SQL, how to populate number in sequence in column without alter the table structure?

1 Answers