SQL Generate Sequence Numbers

Question

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?

It is a simple query like so:

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

I've tried something like and it didn't work:

(Select max(ISNULL(SeqNum, 0)) + 1 From @TempTable Where ID = A.ID)

Any help is very much welcomed. Maybe I have to loop?

Example output I need:

PK  PK
ID  SeqNum
1111    1
1111    2
1111    3
2222    1
2222    2
2222    3
3333    1
4444    1
5555    1
5555    2

Answer 1

If I understand the question, Row_Number() would be a good fit here

Select ID
       ,SeqNum = Row_Number() over (Partition By ID Order By (Select NULL))
 From  ...