How can I get the last inserted primary key?

Question

I'm using SQL Server 2005 with a table with a primary key field with the type varchar(40). I have to get the last inserted record's primary key value. I have tried scope_identity but it is not working. How can I get this?

Answer 1

If your ID is a varchar I suppose it's not auto generated so you should know it before you insert a record . BTW can't you just select your id after you insert a record? Something like this:

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 ID before inserting a new record it can be a little bit harder depending on ID values.

If you can find the last record by sorting ID column you can do it as follows:

SELECT Max(ID) FROM myTable

If not you can have a DateTime filed (CreationDate for example) that holds the time of insertion of the record.Then you can do as follows:

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

Answer 2

Thank you for your reply. I found the result.

Code:

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