SQL incrementing help

Question

 1. Name---ID--- SEQ
 2. *---------101--1
 3. *---------101--2
 4. *---------101--3
 5. *---------999--1
 6. *---------999--2

Iqnore the -'s

What im importing is Name, ID, and other fields. What im trying to assign is SEQ. I'm not sure how to reset a count when I get to the 2nd group. Im using MS SQL Server 2005

Answer 1

In your import you can calculate sequence as follows

INSERT INTO DestTable
SELECT Name, ID,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS SEQ
FROM SourceTable

From your example data it is unclear how you determine within an ID partition what is 1,2,3 etc. Maybe you would need to change the above to PARTITION BY ID ORDER BY Name or something else. At the moment it will be arbitrary.