Unanswered: Increment primary key in Insert statment
I need to create new rows based on rows that already exist, so that there are two entries for each structure_code. I checked the DDL, and the primary key (rate_id) is not set to auto increment. So how can I create unique indexes for the new rows? I am using 10g
This is what I tried so far. I get a unique constraint violation on rate_id
insert into billing_rate
(select max(rate_id) from billing_rate),
from billing_rate where rate_type_code = 'RsCostRate'
You said you want to "create unique indexes", which I assume will be unique keys (being natural or not), so.. I suggest you use a SEQUENCE and then just: (a) use a procedure selecting the sequence when inserting into billing_rate or (b) feed the rate_id from the sequence in a trigger.
The reason you're getting a unique constrain violation is *probably* because rate_id is unique, and you're select'ing the MAX value from it to insert it into the table again. You will have to add some *salt* to it to make it unique, reason I am suggesting you to use a sequence.
The easiest way is to combine sequence with trigger :
- create a sequence on rate_id
- create trigger "ON INSERT" that, at each insert in your billing_rate table, will insert "your_sequence.nextval" in the rate_id column
Thanks to that you can do all your inserts without specifying any value for the rate_id (and not at each time find the max value : it can be long for big tables). The rate_id field will be automatically incremented by the trigger/sequence at each insert