CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
alextc
alextc

Reputation: 3515

Loop through rows and add a number for a column for each of them automatically in SQL Server

I have got an over 500 rows table with a column called ID which is of datetype INT. Currently the values are all NULL.

What I want to achieve is to populate the ID column with an incremental number for each row, say 1, 2, 3, 4, ..., 500 etc.

Please give me a help with any idea how to achieve this by SQL script.

Upvotes: 2

Views: 2204

Answers (2)

John Dewey
John Dewey

Reputation: 7093

using ROW_NUMBER in a CTE is one way, but here's an alternative; Create a new id1 column as int identity(1,1), then copy over to id, then drop id1:

-- sample table
create table myTable(id int, value varchar(100));

-- populate 10 rows with just the value column
insert into myTable(value)
select top 10 'some data'
from sys.messages;
go

-- now populate id with sequential integers
alter table myTable add id1 int identity(1,1)
go
update myTable set id=id1;
go
alter table myTable drop column id1;
go
select * from myTable

Result:

id          value
----------- -------------
1           some data
2           some data
3           some data
4           some data
5           some data
6           some data
7           some data
8           some data
9           some data
10          some data

While you could also drop and recreate ID as an identity, it would lose its ordinal position, hence the temporary id1 column.

Upvotes: 2

sumit
sumit

Reputation: 15464

#create one temporary table
CREATE TABLE Tmp
(
  ID int NOT NULL
         IDENTITY(1, 1),
  field(s) datatype NULL
)

#suppose your old table name is tbl,now pull 
#Id will be auto-increment here
#dont select Id here as it is Null
INSERT  INTO Tmp (field(s) )
        SELECT  
                field(s)
        FROM    tbl

#drop current table
DROP TABLE tbl

#rename temp table to current one
Exec sp_rename 'Tmp', 'tbl'
#drop your temp table
#write alter command to set identitry to Id of current table

good luck

Upvotes: 0

Related Questions