Populate table with auto incrementing number

[Savil]

Hi
I have come into an organisation where all the tables are in a real mess. I would like to start creating primary keys for some tables but am a little stuck on how to cater for existing data.

The triggers and sequences part is no problem. I have created a column called autonumber and now need to populate the existing rows with an incrementing number. I presume a cursor is the way forward but could somebody give me an example please. The table does contain duplicates.

Thank you in advance

 

[ChrisHunt]

You need, I think, to do it in stages.

Add the new column to the existing table. Allow it to be null to begin with, we'll change that in a minute. Create the sequence that will populate it.

Populate the existing rows like this:

UPDATE my_table
SET autonumber = my_sequence.NEXTVAL
WHERE autonumber IS NULL; -- not really necessary, but I'm cautious!

Don't worry, it'll fetch a new value from the sequence for each row of the table.

Make the autonumber field NOT NULL, add a primary key constraint, and set your trigger up to populate the field in future rows. Job done!

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[Savil]

Thanks ever so much for your suggestion Chris

I am running it now on a table of 1.6 million rows. Combined with the rubbish network, DB configuration and my lack of will to live it is taking a while, so if works then there will be a star for you young man which you can wear with pride.

Simeon

 

[Thargy]

savil,

if its taking a while may I suggest that instead of the default sequence cache of 20, you create your sequences with a cache of 100,000, whilst first populating the table's pk column.

Also, if numerous tables have a column called autonumber, it's downright confusing. I limit my table name to 26 characters, and they all have a pk column called <TABLE NAME>_ID (e.g. the table FRED, has a PK called FRED_ID) which derives from a sequence called S_FRED which is activated by a before inster trigger called TBI_FRED. This naming convention applies to all tables, and saves us hours of developing and programming heartache.



Regards

T