Get Inserted auto generated values in SQL

Parthiban Selvaraj
12y
6k
0
2

Blog

Hi,

In most cases, if we want to get the inserted autogenerated values in sql, we will go for

Select Max(ID) from tablename.

This will works fine.

But, the problem is when we have multiple users inserting some values in the same time, then the above query will not give the current value that you inserted.

It will give only the max value when it searches, not exactly your value.

To avoid this and to get the correct value, you can go for @@Identity or SCOPE_IDENTITY()
Try this.

USE AdventureWorks;
GO
--Display the value of LocationID in the last row in the table.
SELECT MAX(ID) FROM Emp;
GO
INSERT INTO Emp (Name, LastName, Age, ModifiedDate)
VALUES ('Parthiban', 'Selvaraj', 25, GETDATE());
GO
SELECT @@IDENTITY AS 'Identity';
GO
--Display the value of LocationID of the newly inserted row.
SELECT MAX(ID) FROM Production.Location;
GO

RESULT SET:
---------

SCOPE_IDENTITY()
SCOPE_IDENTITY returned the last identity value in the same scope. This will not return if we have any other values inserted in trigger.
@@IDENTITY
@@IDENTITY returned the last identity value inserted in the table if inserted by the trigger.

Try this, you can find it easy

/*Create the trigger that inserts a row in table emp1
when a row is inserted in table emp.*/
CREATE TRIGGER Ztrig
ON TZ
FOR INSERT AS 
   BEGIN
   INSERT emp1 VALUES ('check')
   END

/*FIRE the trigger and determine what identity values you obtain 
with the @@IDENTITY and SCOPE_IDENTITY functions.*/
INSERT emp VALUES ('Lajapathy')

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
SELECT @@IDENTITY AS [@@IDENTITY]
GO





Thanks :)