Get the primary key(ID) of the last inserted record in Access and SQL Server

(Obtener la clave primaria(ID) del ultimo registro insertado en Access y SQL Server)

One of the most common things that you have to do when you are working with databases is taking the primary key of the last inserted record. Usually, after making the insert, you make another query with the same parameters that you use in the insert for taking the primary key of the inserted record.

There is 3 solutions:

1) SELECT @@IDENTITY(MS Access and SQL Server)

I have tested it in MS Access and SQL Server with a desktop app and with some store procedures having the correct values in both cases, BUT it doesn't work well in any other cases, because it returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value. So if you made 2 inserts and you want get the id of the first one, Select @@identity returns to you the id of the second insert. Another problem is if you insert a value in a table, and this insertion throws a trigger which makes an insert in another one table. In this case SELECT @@IDENTITY returns the ID of this second table.

2) SELECT IDENT_CURRENT('tablename')(only SQL Server 2000)
This new function returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.

3) SELECT SCOPE_IDENTITY()(only SQL Server 2000)
This new function returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.

For more information read http://www.databasejournal.com/features/mssql/article.php/3307541

Permalink: Get the primary key(ID) of the last inserted record in Access and SQL Server