CODEWITHSUNDEEP
sql-server
Codrin Afrasinei
Codrin Afrasinei

Reputation: 17

Insert consecutive numbers into a table

Let's say I have the following table:

id     name     age
       John     23
       Mary     22
       Mike     25
       etc      etc

I would like to generate consecutive number into id column for every record. Could anyone help me?

Sorry if I asked the same question as asked before.

Upvotes: 0

Views: 9740

Answers (3)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Here is an example of how to update table through CTE:

DECLARE @t TABLE ( ID INT, Name NVARCHAR(50) )

INSERT  INTO @t
VALUES  ( 3, 'a' ),
        ( 5, 'b' ),
        ( 10, 'c' )


SELECT  * FROM    @t;

WITH    cte
          AS ( SELECT   ID ,
                        ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) AS RN
               FROM     @t
             )
    UPDATE  cte
    SET     ID = RN

SELECT  * FROM    @t

Output:

ID  Name
3   a
5   b
10  c

ID  Name
1   a
2   b
3   c

Instead of 

ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) AS RN

you can do 

ROW_NUMBER() OVER ( ORDER BY Name ) AS RN

in order to increment values by ordering in Name column.

Upvotes: 1

Dyrandz Famador
Dyrandz Famador

Reputation: 4525

Alter your table then add identity and it will generate value 

 ALTER TABLE dbo.YourTable
   Add id Int Identity(1, 1)

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use ROW_NUMBER() to add a sequential number:

SELECT
    id = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
    name,
    age
FROM yourTable

Upvotes: 2

Related Questions