How to obtain the automatic ID number of the newly inserted new record in SQL Server

 

---------------------------------------------------------------
Use [ident_current]
Usage: Select ident_current ('tablename ')
---------------------------------------------------------------
Select ident_current ('tablename') -- returns the last automatic ID of the current instance.
Select ident_incr ('tablename') -- returns the increment value.
Select ident_seed ('tablename') -- returns the seed value.

Return the next automatic number:
Select ident_current ('tablename') + (select ident_incr ('tablename '))

-- Create databases and tables
Create Database mydatabase
Use mydatabase

Create Table mytable
(
Id int identity (1, 1 ),
Name varchar (20)
)

-- Execute this SQL statement to check the values of the auto-incrementing column corresponding to the inserted record.
Insert into mytable values ('Li si ')
Select @ identity

Declare @ new_id int
Insert into test_id values ('hahaha ')
Select @ new_id = @ identity
Print 'closest ID is '+ Cast (@ new_id as nvarchar (10 ))