Add IDENTITY or Unique number to a table – T/SQL

10/03/2023
| No Comments

I want to update my table records. However, my existing table does not have any unique columns. So, I need to append Identity column and update all the records based on that Identity column.

For example, If my temp table has 1000 records without any unique column values. I need to number all these 1000 records and update the values.

There is no need to do an UPDATE. The identity column is going to be populated when it is created. All you need is:

ALTER TABLE #temp
ADD Id INT Identity(1, 1)
GO

or when you create a table: 

CREATE TABLE #temp
(
    Name varchar(10)
)
INSERT #temp VALUES ('A'),('B')

--Add identity column
ALTER TABLE #temp ADD ID int IDENTITY

It may add value by adding the identity column as a clustered primary key to make the table more efficient.

ALTER TABLE #temp ADD Id int Identity(1, 1) primary key clustered;
Microsoft SQL, SQL, T-SQL Code
, , ,