issue max value for records inserted all take number 4 i need it 4,5,6,7 ?

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  January 5, 2022 at 11:44 am

  #3970087

  I need to insert data to table #t with incremental by 1

  so max id for records on #t is 3 so

  if i insert multi rows as 4 rows to table #t then it must take 4,5,6,7

  but this not happen all records take 4

  so how to solve this issue please

  i don't need use identity column so how to increment every row by 1 without using identity column

  so how to do that please

   

  create table #t
   (
   id int,
   name nvarchar(20)
   )
   insert into #t(id,name)
   values
   (1,'ahmed'),
   (2,'ali'),
   (3,'ala')
      
   create table #t2
   (
   name nvarchar(20)
   )
   insert into #t2(name)
   select 'islam'
   union
   select 'sayed'
   union
   select 'wahdan'
   union
   select 'warshan'
  

  what i try
  
   insert into #t(id,name)
   select (select isnull(max(id)+1,0) from #t ),name from #t2

   

  expected result

  

  • This topic was modified 3 years ago by  ahmed_elbarbary.2010. Reason: sql

• DesNorton

  SSC-Insane

  Points: 24423

  More actions

  January 5, 2022 at 12:48 pm

  #3970107

  The following code will achieve your requirement.

  However, you may want to add code to prevent duplicates

   INSERT INTO #t( id, name )
   SELECT ISNULL((SELECT MAX(id) FROM #t), 0) + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        , name
   FROM #t2;
  

  ---

  How to post data/code on a forum to get the best help.

• Grant Fritchey

  SSC Guru

  Points: 398913

  More actions

  January 5, 2022 at 3:13 pm

  #3970166

  I like @desnorton's answer.

  However, what about just using the SEQUENCE object? It'll do the increments for you. It doesn't have to be specifically an IDENTITY column that way.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• Jeff Moden

  SSC Guru

  Points: 1004434

  More actions

  January 5, 2022 at 10:27 pm

  #3970326

  Building your own sequential numbers is a sure fire way to generate duplicates by accident.  Even Des Norton notes that for his code.

  Please explain why you're trying to avoid an IDENTITY column because that, or as Grant mentions, a SEQUENCE are the 2 best ways there are.  What is it about IDENTITY that you're trying to avoid?

  We need to know such things because if, for example, you're trying to avoid gaps in the sequence, neither IDENTITY nor SEQUENCE will guarantee that.  There IS a way to avoid such things as GAPs but I don't want to waste my time if that's not the reason why you're trying to avoid IDENTITY.

   

   

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)