title: Use ROW_NUMBER to increment by 100?
body: <p>I have an existing table where a line sequence number is incremented by 100 in normal software operation. I need to perform a large insert and maintain this method of incrementation. I thought I would use ROW_NUMBER() but I do not know if it can increment in this way. </p>
  
  <p>I'd appreciate help on that simple topic, however, if you want to get more specific please also note that the line sequence is unique for a Project and some records already started the sequence. For example</p>
  
  <pre><code>Project  Category  Sequence
  Proj1    Cat4      100
  Proj1    Cat2      200
  Proj2    Cat7      100
  Proj2    Cat1      200
  Proj3    Cat1      100
  </code></pre>
  
  <p>So if these exist already and I am inserting new records, I have to account for the sequence already started for each project. So if I insert rows for Proj1, the Sequence value must be 300, 400, ...</p>
  
  <p>Here is my block of code so far:</p>
  
  <pre><code>WITH CTE (PROJECTNUM, COSTCATID, LINE) AS
  (  SELECT PROJECTNUM, PACOSTCATID, ROW_NUMBER() OVER(PARTITION BY PROJECTNUM ORDER BY PACOSTCATID) 
        + (SELECT MAX(LNITMSEQ) FROM PROJECTTABLE WHERE PAPROJNUMBER = A.PROJECTNUM)
     FROM PROJECTSHORTLIST A, CATEGORYTABLE
     WHERE &lt;stuff you don't care about&gt;
  )
  INSERT INTO PROJECTTABLE
  (PAPROJNUMBER, PACOSTCATID, LNITMSEQ)
  SELECT PROJECTNUM, COSTCATID, LINE
  FROM CTE;
  </code></pre>
  
  <p>You can see this will produce records incrementing by one and for Proj1 would be 301, 302. </p>
  
  <p>Your brain power is greatly appreciated.</p>
  
  <p>Update: I will add that if this incrementation by 100 can be more easily done in an update statement after the rows are first inserted that is perfectly acceptable. Thank you.</p>