SQL Server: insert the next available int

Question

I am dealing with a table in SQL Server that has a serial_no column that is defined as non null int. It is not an automatically incrementing field, as if I left this column from my insert statement, I get an error message saying that the serial_no field cannot be null.

So how do I insert the next available number?

I tried this:

 INSERT INTO mytable (serial_no) VALUES ( (SELECT MAX(serial_no)+1 FROM mytable)) 

but I get an error that subqueries cannot be used in this context.

EDIT: This table is used in the finished product, so I can’t change the design and make the serial_no column automatically increment.

Answer 1

You can improve concurrency notation with keyboard shortcuts

 INSERT INTO mytable (serial_no, value) SELECT MAX (serial_no)+1, @value FROM mytable WITH (ROWLOCK, XLOCK, HOLDLOCK) 

If performance is not important, try TABLOCKX instead of ROWLOCK, XLOCK

However, given that this is unsafe, you need to try again

 DECLARE @retry bit SET @retry = 1 WHILE @Retry = 1 BEGIN BEGIN TRY INSERT INTO mytable (serial_no, value) SELECT MAX (serial_no)+1, @value FROM mytable WITH (ROWLOCK, XLOCK, HOLDLOCK) SET @Retry = 0 END TRY BEGIN CATCH IF ERROR_NUMBER() <> 2627 --PK violation RAISERROR ('blah', 16, 1) END CATCH END 

Or go to the IDENTITY column and do it right ...

Answer 2

The error can be fixed by resetting the VALUES values.

 INSERT INTO mytable (serial_no, value) SELECT MAX(serial_no)+1 , @value FROM mytable) 

But this is a bad idea. There, the race condition for MAX (serial_no) +1 (for example, two inserts have the same value for Max (Serial_no).

You better use the auto increment field. You can also create a table that stores the current next value and increment it, instead of using max.

Answer 3

 INSERT INTO mytable (serial_no) SELECT MAX(serial_no)+1 FROM mytable 

Answer 4

Try it without VALUES:

 INSERT INTO mytable (serial_no) SELECT MAX(serial_no)+1 FROM mytable