Insert and increment sequence#

• TSQLExplorer

  Ten Centuries

  Points: 1279

  More actions

  January 20, 2006 at 12:36 pm

  #90655

  I'm inserting rows from one table to another based on parameters and need to during the insert process increment a sequence number for each inserted record based on an already existing record with matching field (by 1) or if no existing record, set the sequence to "1".    I'm thinking this will involve a "CASE" and "MAX +1" line, and some variables (which I'm light on).   Any thoughts on this would be appreciated..

   

  Thanks!

• PW-201837

  SSC-Insane

  Points: 20805

  More actions

  January 20, 2006 at 1:48 pm

  #616220

  LEFT JOIN to a derived table of the current max per match column:

  INSERT INTO Table1

    (Sequence, OtherColumns)

  SELECT 

     ISNULL( dt.CurrentSeq, 0) + 1, OtherColumns

  FROM Table2

  LEFT JOIN

  (

    SELECT MatchColumn, Max(Sequence) As CurrentSeq

    FROM Table1

    GROUP BY MatchColumn

  ) dt

    On (dt.MatchCOlumn = Table2.MatchCOlumn)

   

   

• TSQLExplorer

  Ten Centuries

  Points: 1279

  More actions

  January 20, 2006 at 1:56 pm

  #616224

  That seems  a very elegant way to do this. thank you...

   

  In addition to the records I'll be inserting with this proc ... there are already existing records put in manually by the operators with creation dates after those dates in the records I'm putting in.. which would result in the student's record set having dates which are not in the same order as their sequence number, which is required for our purposes.

  so...

  If I wanted, once the insert process is done, to go back to the target table and re-sequence all "sequence" numbers for each student (based upon stulink number) so the sequence numbers would match the ascending date order for each student, could a derivative of your proc work?  

  Thanks...