How to add Row Number

Question

User1979860870 posted

Hi

  In below code i want to add Row Number in Code field. Code is Primary field & i don't want to give it Identity .

There is no column for OrderBy

INSERT INTO [tbl1] (Code,Name)
(Select rownumber,Name from tbl2)

Thanks

Answer 1

User452040443 posted

Hi,

Try:

INSERT INTO [tbl1] (Code,Name)
  Select 
    row_number() over(order by (select null)),
    Name 
  from tbl2

Hope this help

Answer 2

User1979860870 posted

Hi imapsp

  In all records it is showing value 1

Thanks

Answer 3

User452040443 posted

Hi,

Are you sure? How many rows have tbl2? The Row_Number function must return a different number for each row.

Answer 4

User1535942433 posted

Hi jagjit saini,

In all records it is showing value 1

Are all code filed value 1?I have created and it works.Just like this:

create table GT(
   UserName varchar(50) NOT NULL PRIMARY KEY
)
insert into GT(UserName) values('Test1')
insert into GT(UserName) values('Test2')

create table GT2(
    Code varchar(50),
    Name varchar(50)
)

insert into GT2(Code,Name)
Select 
    row_number() over(order by (select null)),
    UserName 
from GT

Result:

Could you post your data structure to us?And you could refer to below article:

https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver15

Best regards,

Yijing Sun

Answer 5

User1979860870 posted

Hi imapsp

  There are more than 10000 records

Thanks

Answer 6

User1979860870 posted

Hi imapsp

  There was some bracket issue. resolved

Thanks