How can I get the last inserted primary key?

Question

I am using SQL Server 2005 with a table with a primary key field of type varchar(40) . I should get the last inserted value of the primary key of the record. I tried scope_identity but it does not work. How can i get this?

Answer 1

I have found the result.

Code:

 insert into T output inserted.pk values ('new item'); 

Answer 2

If your identifier is varchar, I assume that it is not generated automatically, so you should know it before inserting a record. By the way, can you just select your identifier after entering the entry? Something like that:

 CREATE PROC InsertXXX( ... value parameters...) AS BEGIN INSERT ..... SELECT ID FROM MyTable END 

UPDATED:

If you want to know the last inserted record and its identifier before inserting a new record, this can be a little more complicated depending on the identifier values.

If you can find the last entry in the sort ID column, you can do it like this:

 SELECT Max(ID) FROM myTable 

If you cannot register a DateTime (e.g. CreationDate), which contains the insert time of the record. Then you can do the following:

 SELECT ID FROM MyTable WHERE CreationDate=(SELECT Max(CreationDate) FROM MyTable)