SQL: Auto Increment value during insert

Question

I have the an existing table that for some reason the designer decided to manually control the Primary Key value by storing the last used value in a seperate table (changing the table to use Identity is not an option right now).

I now need to do a mass update to this table as follows:

DECLARE @NeedFieldID int
SET @NeedFieldID = 62034

    INSERT INTO T_L_NeedField (NeedID, NeedFieldID, FieldName, Sequence, DisplayAs, FieldPrompt, DataType, ValidOptions, IsRequiredForSale)
    (
        SELECT 
            DISTINCT n.NeedID, 
                @NeedFieldID + 1,           
            'DetailedOutcome',
            999,
            'Detailed Outcome',
            'Select appropriate reason for a No Sale outcome',
            'T',
            'Pricing, Appointment Date / Time Not Available, Will Call Back, Declined', 
            0
        FROM  T_L_Need n
            INNER JOIN T_L_NeedField nf
                ON n.NeedID = nf.NeedID
        WHERE (n.Need LIKE 'Schedule%' AND n.Disabled = 0)
    )

Obviously '@NeedFieldID + 1' doesn't work (just using it to show what I want to do). How can I increment @NeedFieldID as SQL inserts the values for each of the distinct NeedId's? I am using SQL Server 2008.

Solution

You want row_number():

DECLARE @NeedFieldID int
SET @NeedFieldID = 62034

    INSERT INTO T_L_NeedField (NeedID, NeedFieldID, FieldName, Sequence, DisplayAs, FieldPrompt, DataType, ValidOptions, IsRequiredForSale)
    (
        SELECT 
            DISTINCT n.NeedID, 
                @NeedFieldID + row_number() over (order by n.NeedID),           
            'DetailedOutcome',
            999,
            'Detailed Outcome',
            'Select appropriate reason for a No Sale outcome',
            'T',
            'Pricing, Appointment Date / Time Not Available, Will Call Back, Declined', 
            0
        FROM  T_L_Need n
            INNER JOIN T_L_NeedField nf
                ON n.NeedID = nf.NeedID
        WHERE (n.Need LIKE 'Schedule%' AND n.Disabled = 0)
    )

However, your best bet is to make NeedFieldID an identity column and just let SQL Server do the work for you.