CODEWITHSUNDEEP
sqlsql-server
Tech Learner
Tech Learner

Reputation: 1317

SQL - Append Identity Column in Existing Temp table

I want to update my temp table records. But my existing Temp table does not have any unique column. 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 values. 

while(@count < identity_value)
begin
update #temp
Name = 'Gold'
where identity = @count
@count = @count+1
End

I can Alter table option but in my case records are already inserted into my temp table. So I need to loop thorugh it by adding Identity column.

Upvotes: 1

Views: 2378

Answers (3)

SteveD
SteveD

Reputation: 867

It may add value to add 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;

Upvotes: 1

Paweł Dyl
Paweł Dyl

Reputation: 9143

You don't need loops. See this simplified example:

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

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

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

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

Id field will be populated and it will hold values 1, 2, ..., 1000.

Upvotes: 2

Related Questions