SQL SERVER 2008 GET LAST INSERTED VALUES

Question

This is my table.

Ticket(id int auto_increment,name varchar(50));

after inserting into this table i want send id and name to mail.. how can i get the last Inserted value.

help to solve this...

Antoninaantonino

Answer 1

Look into: Scope_identity

SCOPE_IDENTITY()

The table which is having Identity Property from that table we can get the last inserted record id will be like this

SELECT SCOPE_IDENTITY()

OR

But this is not a safe technique:

SELECT MAX(Id) FROM Ticket

OR

This is also not a safe technique:

SELECT TOP 1 Id FROM Ticket ORDER BY Id DESC

Murder

Your second and third suggestions are not safe under conditions of concurrency. – Pulver

This will work but there will be a problem when simultaneously more than one entry is inserted in the table. e.g., At the time you fetch the record, another record is inserted in-between then you wont get the correct result so this is under condition of concurrency...Hope now you are clear..:)...@DavidA – Murder

Answer 2

You should use SCOPE_IDENTITY() to get last primary key inserted value on your table. I guess it should be the ID value. Once you have it, do a SELECT using this ID and there you have it.

There is also @@IDENTITY but there are some differences using one or the another that could lead to inaccuracies on the values.

Check these detailed articles for more insights, a detailed description on how to use them, why they are different and some demo code:

• For the last time, NO, you can't trust IDENT_CURRENT()

• sql server identity vs scope_identity vs ident_current retrieve last inserted identity of record

Imprimis

Answer 3

For SQL Server before 2008 R2 Service Pack 1 (Link):

You may receive incorrect values when using SCOPE_IDENTITY() and @@IDENTITY

In that case, the OUTPUT clause is the safest mechanism:

DECLARE @InsertedRows AS TABLE (Id int)
DECLARE @NewId AS INT
INSERT INTO HumanResources.Employees
  ( /* column names */)
OUTPUT Inserted.Id INTO @InsertedRows
  VALUES (/* column values */)
SELECT @NewId = Id FROM @InsertedRows

Areopagus

Is this needed within a transaction? – Oakie

Answer 4

add auto increement Field in your table like.... index (INT,AUTO INCREEMENT) then before insert or any operation done c get your last record by getting max(index)

Fiorenze

Please fix the syntax to let people understand and try what you are suggesting. – Panjabi

Answer 5

this might also helpful in some scenarios

SELECT Top 1 CAST(Id AS int) as ID from table ORDER BY [Id] DESC;

Notate