How to get the next ID on INSERT?

Question

I want the identifier column to be enlarged for each insert in the table.

I tried this statement:

INSERT INTO Anlagenteil (ID, TaId, Subtype, Name) VALUES (MAX(ID)+1, 0, 'BdAnlageteil', 'Barcodeleser0'); 

Sorry, I get this error message:

 Msg 207, Level 16, State 1, Line 1 Invalid column name 'ID'. 

Answer 1

Use the subquery as follows:

 INSERT INTO Anlagenteil (ID, TaId, Subtype, Name) VALUES ((SELECT ISNULL(MAX(ID) + 1, 1) FROM Anlagenteil), 0, 'BdAnlageteil', 'Barcodeleser0'); 

Answer 2

Invalid column name 'ID'.

This means that you do not have an ID column.

You should add this column to the table and set it to auto-grow, instead of writing logic to do it yourself.

As @Damien_The_Unbeliever noted, this can cause problems if two people run the script at the same time.

 ALTER TABLE Anlagenteil ADD ID INT IDENTITY(1,1) 

Then your SQL statement could be simple:

 INSERT INTO Anlagenteil (TaId, Subtype, Name) VALUES (0, 'BdAnlageteil', 'Barcodeleser0') 

And a new ID value will be added automatically.