Populating Sequence values in a column to an already existing table

Recently we got a requirement to add a new identity column to an already existing table and to assign the sequential values to it. The table has thousands of rows already and manually entering the values into this column isn’t the right solution. The below query which automatically updates all the rows in a single shot has done the trick.

To do this, add the required column and update the column with the below query (change the table name and the column name as per your context) before making it as Identity column.

declare @value int=0;
update MyTable set @value=MyTableId=@value+1

 

Hope this helps

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s