How to get next ID upon INSERT?

Question

I want to make sure the ID column is incremented for every insert on a table.

I tried this statement:

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

Unfortunately I get this error message:

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

Answer 1

Use nested query like this:

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 suggests you don't have an ID column at all.

You should add this column to the table, and set it to auto-increment, rather than writing the logic to do this yourself.

As @Damien_The_Unbeliever has pointed out, this could cause issues if 2 people run the script at the same time.

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

Then your SQL statement can just be:

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

And the new ID value will automatically be added.