CODEWITHSUNDEEP
sqlsql-servert-sql
SOMEGUYIKNOW
SOMEGUYIKNOW

Reputation: 5

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!

Upvotes: 0

Views: 110

Answers (2)

Ben Thul
Ben Thul

Reputation: 32687

Seconding a recommendation from the comments above, we use Sequences in our production system with no problem. Here's how it looks:

create sequence SQ_Extender_Record_ID
    minvalue 1
    start with 1
    cache 100;

INSERT INTO EXT01100 (Extender_Record_ID, Extender_Window_ID, Extender_Key_Values_1 , Extender_Key_Values_2, Extender_Key_Values_3)
    SELECT (next value for SQ_Extender_Record_ID), 'ECO_FEE_DIGIT', SOL.LNITMSEQ, SOL.SOPNUMBE, SOL.SOPTYPE
        FROM SOP10200 SOL
        WHERE SOL.SOPTYPE = @InTYPE AND SOL.SOPNUMBE = @INNUMBE AND SOL.LNITMSEQ <> 0

Obviously, adjust the min/start values as appropriate for your situation.

If you want, you could add a default constraint to the table/column with this:

alter table EXT01100 add constraint DF_EXT01100__Extender_Record_ID
   default (next value for SQ_Extender_Record_ID)
   for Extender_Record_ID

You mention that this is in a database whose schema you don't control, so that may not be an option; I mention it for the sake of completeness.

Upvotes: 1

Dale K
Dale K

Reputation: 27202

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;

Upvotes: 3

Related Questions