when you create your table, define your auto incrementing column with a type of INT IDENTITY(1,1) that means that it starts at 1 and increments by 1. You do not need to specify the column during the INSERT.

CREATE TABLE TEST_TABLE(
   ID INT NOT NULL IDENTITY(1,1) 
   ...
 )

After inserting you can call a SQL function named SCOPE_IDENTITY() and it will return to you the last integer assigned using the current connection.

What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current()?