title: SQL Generate Sequence Numbers
body: <p>I have a table with 2 PK Columns that I need to populate. The first column "ID" is already populated, I need to generate and populate the "SeqNum" column with int's like in my example below. There obviously cannot be the same SeqNum in the same record as the same ID because they are both PK's(I'm sure you know what I mean). How can I accomplish this?</p>
  
  <p>It is a simple query like so:</p>
  
  <pre><code>Insert into @TempTable
  Select A.ID
        ,1 --not sure what to do here
        ,B.Col3
        --and other columns
  From Blah A Join Blah2 B on B.ID = A.ID
  </code></pre>
  
  <p>I've tried something like and it didn't work:</p>
  
  <pre><code>(Select max(ISNULL(SeqNum, 0)) + 1 From @TempTable Where ID = A.ID)
  </code></pre>
  
  <p>Any help is very much welcomed.
  Maybe I have to loop?</p>
  
  <p>Example output I need:</p>
  
  <pre><code>PK  PK
  ID  SeqNum
  1111    1
  1111    2
  1111    3
  2222    1
  2222    2
  2222    3
  3333    1
  4444    1
  5555    1
  5555    2
  </code></pre>