CODEWITHSUNDEEP
sqlsql-servert-sql
Sal
Sal

Reputation: 305

Adding ascending numbers to a rows of data

I have a column named ROWID and I'd like to add ascending numbers to it... to be used as a PK. What script can I run that will just automatically just add numbers 1 through whatever? I know since its not an Identity column and seeded it wont increase with updates.

So basically I'm looking for a fill command that with just add ascending numbers.

Thanks...

Upvotes: 0

Views: 2626

Answers (2)

M.Ali
M.Ali

Reputation: 69524

As you have mentioned this column is to be used as a Primary key so hopefully you wont have any duplicates in that column. Assuming this that you dont have any duplicates in this column the following query will work just fine.

;WITH CTE
AS
 (
  SELECT ROWID, ROW_NUMBER() OVER (ORDER BY ROWID ASC) AS RN
  FROM TABLE_NAME
 )
UPDATE CTE
SET ROWID = RN

And since you want this column to be sequential auto increment why not just add an identity column. Unfortunately you can make an existing column Identity column, So you will need to create another column with Identity.

Something like this..

--Drop the existing Column 1st

ALTER TABLE Table2
DROP COLUMN  ROWID 

--Create a new column and make it Identity column

ALTER TABLE Table2
ADD  ROWID INT NOT NULL IDENTITY(1,1)

Upvotes: 0

NiVeR
NiVeR

Reputation: 9786

You are looking for the AUTOINCREMENT keyword. More details here.

Upvotes: 1

Related Questions