How to add Row Number[Answered] RSS

• • Reply
  jagjit saini

  Member

  24 Points

  91 Posts

  How to add Row Number

  Dec 08, 2020 05:40 AM|jagjit saini|LINK

  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

• • Reply
  imapsp

  Participant

  1120 Points

  291 Posts

  Re: How to add Row Number

  Dec 08, 2020 01:16 PM|imapsp|LINK

  Hi,

  Try:

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

  Hope this help

  https://www.imoveisemexposicao.com.br/imoveis-venda-guarulhos-residencial-casa
• • Reply
  jagjit saini

  Member

  24 Points

  91 Posts

  Re: How to add Row Number

  Dec 08, 2020 03:33 PM|jagjit saini|LINK

  Hi imapsp

    In all records it is showing value 1

  Thanks

• • Reply
  imapsp

  Participant

  1120 Points

  291 Posts

  Re: How to add Row Number

  Dec 08, 2020 05:51 PM|imapsp|LINK

  Hi,

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

  https://www.imoveisemexposicao.com.br/imoveis-venda-guarulhos-residencial-casa
• • Reply
  yij sun

  Contributor

  3390 Points

  1284 Posts

  Re: How to add Row Number

  Dec 09, 2020 03:38 AM|yij sun|LINK

  Hi jagjit saini,

  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

  .NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
• • Reply
  jagjit saini

  Member

  24 Points

  91 Posts

  Re: How to add Row Number

  Dec 09, 2020 04:42 AM|jagjit saini|LINK

  Hi imapsp

    There are more than 10000 records

  Thanks

• • Reply
  jagjit saini

  Member

  24 Points

  91 Posts

  Re: How to add Row Number

  Dec 09, 2020 04:47 AM|jagjit saini|LINK

  Hi imapsp

    There was some bracket issue. resolved

  Thanks