Need to generate IDs from the MAX(ID) of a CTE

• GBeezy

  SSCarpal Tunnel

  Points: 4723

  More actions

  October 17, 2022 at 6:19 pm

  #4100629

  WITH MAXPRedID as (
  
  SELECT MAX(ID) + 1 AS MaxID, SystemID
  
  FROM dbo.TableA
  
  GROUP BY SystemID)
  
  SELECT a.MaxID + COUNT(B.*), b.*
  FROM dbo.TableB b
  INNER JOIN MaxPredID a
  ON b.SystemID = a.SystemID 
  

  If my MaxID is 15001 and I have 25 rows in tableB, I need to generate the new 25 IDs for starting at 15001 and ending at 15026.

  Using simplistic numbers in this example, could be 2500 rows from tableb, etc

   

  I have looked into a row_number function, doing a union all in an attempt to generate these all with no luck.

   

  A push in the right direction would be greatly appreciated.

• ratbak

  Ten Centuries

  Points: 1381

  More actions

  October 17, 2022 at 7:21 pm

  #4100674

  1. Is there a reason you can't use a sequence?
  2. Cross apply to Tally table or tally function where Number <= 25 if 1-based or 24 if 0-based

• Ken McKelvey

  SSCoach

  Points: 18853

  More actions

  October 17, 2022 at 7:46 pm

  #4100676

  What did you not understand about the following?

  https://www.sqlservercentral.com/forums/topic/increment-max-value-by-over-a-complete-dataset

• GBeezy

  SSCarpal Tunnel

  Points: 4723

  More actions

  October 17, 2022 at 7:52 pm

  #4100677

  This is actually perfect Ken, thank you!

   

  I must have missed the notification for this reply as I am just now seeing it.

   

  Thank again.

• jcelko212 32090

  SSCrazy Eights

  Points: 9303

  More actions

  October 17, 2022 at 10:05 pm

  #4100726

  Use CREATE SEQUENCE See https://www.red-gate.com/simple-talk/databases/sql-server/learn/sql-server-sequence-basics/

  Please post DDL and follow ANSI/ISO standards when asking for help.