SQL - Inserting a row and returning primary key

For SQL Server 2005 and up, and regardless of what type your primary key is, you could always use the OUTPUT clause to return the values inserted:

INSERT INTO dbo.YourTable(col1, col2, ...., colN)
OUTPUT Inserted.PrimaryKey
VALUES(val1, val2, ....., valN)

SQL Server:

You can use @@IDENTITY. After an insert statement, you can run:

select @@identity

This will give you the primary key of the record you just inserted. If you are planning to use it later, I suggest saving it:

set @MyIdentity = @@identity

If you are using this in a stored procedure and want to access it back in your application, make sure to have nocount off.

For MS SQL Server:

SCOPE_IDENTITY() will return you the last generated identity value within your current scope:

SELECT SCOPE_IDENTITY() AS NewID

Related