SQL: How to get the primary key of a record after the insert without using Select

less than 1 minute read

Question: How to fetch the automatically incremented primary key after an insert without selecting that particular column?

Solution: Use @@Identity global variable to find the newly inserted identity/primary key.

In the following example we have a simple table with two columns

  1. Name : nchar(10)

  2. Id : int , primary key, auto increment = true

lets define a stored procedure to insert values into the table

create proc updatetable (@name nchar(10))
INSERT INTO [Aesop].[dbo].[Person]
return @@IDENTITY

And when you execute the above defined procedure, your output variable would contain the newly inserted primary key

Declare @name nchar(10), @return int,
set @name=pratap;
exec @return = updatetable @name;
print @return

More information about the system functions/variables could be found here

Tags: ,



Leave a comment