Generating New ID

[xor_ax_ax]

I am new to SQL server.
In MySQL when inserting a new record, im locking the table first,
use max(ID) + 1 to get a new ID and then release lock after
succesfully insert a record.

How you do this in MS SQL ?
I try to use subquery like

Insert Into StockGroup Values((SELECT max(id) FROM stockgroup) +1, "test")

Sub query not allowed.

Please help.
Also how get it work in Store Procudure.

Thanks.

[JMvVliet]

INSERT INTO StockGroup (id, otherfield, and_so_on) SELECT MAX(id) + 1, "otherfield", "and_so_on" FROM StockGroup

where 'otherfield' and 'and_so_on' are strings.
This is the right syntaxis.

[mendhak]

You could also consider using two queries, one for determining the max(id) and the other for the insertion.


Another (crude) method is to use SQL Server's own version of autonumber. In the design view of the table, allow for increment and seed of 1.

[xor_ax_ax]

Originally posted by mendhak
You could also consider using two queries, one for determining the max(id) and the other for the insertion.

I wonder how to prevent other user from getting max(ID) when
there are still another user did the same.
As I told before I did lock entire table in mySQL to complete this
task. Table is locked when retrieving max(ID) and inserting a new
record.
Autonumber still the best ?

[JMvVliet]

In my opinion autonumber is the best.

[blaff]

Another (crude) method is to use SQL Server's own version of autonumber.

Why crude??
Normally this is all you need! And you don't have to worry about multiple users getting the same ID, you don't have to lock the table...

[xor_ax_ax]

How do you reset autonumber ?

[Dalceon]

hmm
create a new table just like the one you had??
do a search, this question has been asked many times.