Insert Does Not Increment Primary Key Correctly

Question

I need to figure out how to insert into this table without breaking the application that runs on it. When I run an insert statement outside the application, I select the max uid (which is the indexed primary key) and add one, then insert the data with that uid.

The application is our ERP and was purchased from a vendor. Their support does not extend to SQL help so I have no way of seeing how it is doing the insert. There are no sequences in the database at all either.

Something does not make it down to the application though and it will not allow new inserts into the table through the application until I delete the rows I inserted through SQL. It gives an SQL error that the primary key customer_salesrep_uid is already taken. The application does not seem to know about the inserts I made outside of it and the increases to the uid field. There must be a counter somewhere that I need to update? The index should update by itself on insert.

Update 8/10 re comments: Thank you all for your advice. I will try the profiler and also will try inserting in test with proper locks, with a lower uid than max, and with a much higher uid than max and see if I can glean any further clues from that.

Table Structure:

    Column_name Type    Computed    Length  Prec    Scale   Nullable    TrimTrailingBlanks  FixedLenNullInSource    Collation
    customer_salesrep_uid   int no  4   10      0       no  (n/a)   (n/a)   NULL
    company_id  varchar no  8                   no  yes no  SQL_Latin1_General_CP1_CI_AS
    customer_id decimal no  9   19      0       no  (n/a)   (n/a)   NULL
    salesrep_id varchar no  16                  no  yes no  SQL_Latin1_General_CP1_CI_AS
    commission_percentage   decimal no  9   19      4       no  (n/a)   (n/a)   NULL
    primary_salesrep_flag   char    no  1                   no  yes no  SQL_Latin1_General_CP1_CI_AS
    row_status_flag int no  4   10      0       no  (n/a)   (n/a)   NULL
    date_created    datetime    no  8                   no  (n/a)   (n/a)   NULL
    created_by  varchar no  255                 no  yes no  SQL_Latin1_General_CP1_CI_AS
    date_last_modified  datetime    no  8                   no  (n/a)   (n/a)   NULL
    last_maintained_by  varchar no  255                 no  yes no  SQL_Latin1_General_CP1_CI_AS

No Identity columns

Indexes:

    index_name  index_description   index_keys
    ak_customer_salesrep    nonclustered, unique, unique key located on PRIMARY company_id, customer_id, salesrep_id
    pk_customer_salesrep    clustered, unique, primary key located on PRIMARY   customer_salesrep_uid

Answer 1

The application is our ERP and was purchased from a vendor. Their support does not extend to SQL help so I have no way of seeing how it is doing the insert. There are no sequences in the database at all either.

You could run a trace using SQL Profiler to catch the exact statement that your application is executing.

Also, a slight alteration to Scott's suggestion, I wouldn't use a TABLOCK, instead, something like:

BEGIN TRANSACTION
SELECT @customer_salesrep_uid = max(customer_salesrep_uid) 
    FROM YourTable WITH (XLOCK, READPAST)...

This should reduce the risk of locks on the table.

Answer 2

They could have a custom-made sequence implementation in the form of a dedicated table with an integer column to store the current sequence value. Their inserts could be wrapped into a transaction and go like this:

BEGIN TRANSACTION;

DECLARE @Sequence TABLE (NewValue int);

UPDATE dbo.Sequence
SET CurrentValue += 1
OUTPUT inserted.CurrentValue INTO @Sequence (NewValue);

INSERT ...  /* using @Sequence.NewValue */

COMMIT TRANSACTION;

Depending on how many other tables could use this method, there might be multiple such tables – or there could be just one but with either a row or a column per table.

Answer 3

Perhaps the application is sneaking in between your select and your insert.

It sounds like you need to put your select/insert into a transaction and apply a table lock during the process. EX:

BEGIN TRANSACTION
select @customer_salesrep_uid = max(customer_salesrep_uid) from YourTable WITH (TABLOCKX,HOLDLOCK)
set @customer_salesrep_uid += 1
insert into YourTable... new row...
COMMIT

Answer 4

If you're worried about not having SQL support, just wait until they realise you're hacking data into the DB. I'd put money on them refusing you any other support from that point on.

That said, it sounds like somewhere there is a "max ID" table for all the other tables and you're hitting a problem there where its idea of "max ID" is wrong because you've taken it. Of course that's just a guess.

Also, the max+1 is really the wrong approach both from you and the vendor. It causes contention if you can do it right and duplicate errors when you can't. The best way is sequences or identity.