Add column with row_number in existing table.

Database
ss
Fan

Add column with row_number in existing table.

Team, 

I need to add a column in existing table as row number. How do I do this with alter command.

 

Thank you

2 REPLIES
Apprentice

Re: Add column with row_number in existing table.

Hi,

I don't think you can do this directly using the ALTER TABLE (AT) command.

The AT command will allow you to add a new column and specify a default value, but that value has to be 'hard coded' in the AT statement itself, you can't use an expression or calculation.

What I think you'll have to do is either:

  1. Create a new table and copy  the data across (either use 'CREATE TABLE AS' or 'CREATE TABLE' followed by 'INSERT/SELECT'). In the operation when you copy the data across generate the new data value that you want.
  2. Use ALTER TABLE to add the new column with a default value of NULL. Then run an UPDATE to set the data value of this new column on every row.

In most situations you'll find that option#1 above is quickest.

 

Does that help?

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
ss
Fan

Re: Add column with row_number in existing table.

Thank you @DaveWellman - Option one looks good to me. Thank you so much for your quick response. I will try this. I believe this will solve my issue. Thank you so much.