Inserting an ID that increments (but is not an AUTOINCREMENT)

I have a table that I am trying to insert multiple records into using a select statement.

The ID field is an INT and not autoincremented but I do need to increment in in the INSERT.

The table belongs to a third party product we use for our ERP so I cannot change the property of the ID.

The insert is supposed to create a record in the EXT01100 table for each line item on a particular sales order.

Here is the code I am using:

INSERT INTO EXT01100 (Extender_Record_ID, Extender_Window_ID, Extender_Key_Values_1 , Extender_Key_Values_2, Extender_Key_Values_3)
    SELECT (SELECT MAX(EXTENDER_RECORD_ID) + 1 FROM EXT01100), 'ECO_FEE_DIGIT', SOL.LNITMSEQ, SOL.SOPNUMBE, SOL.SOPTYPE
        FROM SOP10200 SOL WITH(NOLOCK)
        WHERE SOL.SOPTYPE = @InTYPE AND SOL.SOPNUMBE = @INNUMBE AND SOL.LNITMSEQ <> 0 

This works on a single line order, but multiple line orders will produce a Primary Key duplicate error so I don’t think I can use (SELECT MAX(EXTENDER_RECORD_ID) + 1 FROM EXT01100) in this case.

This is in SQL server.

Any help is greatly appreciated!

Answer

You can use row_number to ensure each row has a unique ID, and you need to take an exclusive lock on your main sequence table, and you need to remove your nolock.

INSERT INTO EXT01100 (Extender_Record_ID, Extender_Window_ID, Extender_Key_Values_1 , Extender_Key_Values_2, Extender_Key_Values_3)
    SELECT (SELECT MAX(EXTENDER_RECORD_ID) FROM EXT01100 WITH (TABLOCKX)) + ROW_NUMBER() OVER (ORDER BY SOL.LNITMSEQ)
      , 'ECO_FEE_DIGIT', SOL.LNITMSEQ, SOL.SOPNUMBE, SOL.SOPTYPE
    FROM SOP10200 SOL
    WHERE SOL.SOPTYPE = @InTYPE AND SOL.SOPNUMBE = @INNUMBE AND SOL.LNITMSEQ <> 0;