SELECT @@IDENTITY
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.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.
SELECT SCOPE_IDENTITY()
It 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.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.
SELECT IDENT_CURRENT(‘tablename’)
It 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.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.
To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.
Reference : Pinal Dave (https://blog.sqlauthority.com)
230 Comments. Leave new
@Harinath,
Were you able to log into SQL Server and you cannot access database ?
Please post exact error message.
What is state of database, is it online /suspect/restoring ?
Regards,
IM
Hi,
I need to know, when i should use @@identity or scope_identity() inside any scope. What criteria i should keep in mind when i have to use any one of them in my program.
Thanks
Anil kumar dubey
Read the article again. It was already explained
Great post. Exactly what I was looking for.
i have used
SELECT IDENT_CURRENT(dbo.ITA_STORICO_ORDINI_DISTINTA)
but i have received this error
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier “dbo.ITA_STORICO_ORDINI_DISTINTA” could not be bound.
??????
You need to use a single quote
SELECT IDENT_CURRENT(‘dbo.ITA_STORICO_ORDINI_DISTINTA’)
SELECT CONVERT(INT, SCOPE_IDENTITY()) AS ID
will return the scope identity in for OdbcCmd object. Just doing a SELECT SCOPE_IDENTITY() results type cast errors if you are trying to get the ID out like this
ID = (int) cmd.ExecuteScalar();
@Mauro
Table name should be in single quotations like this,
SELECT IDENT_CURRENT(‘dbo.ITA_STORICO_ORDINI_DISTINTA’)
Try this, Should work.
IM.
hello ..
i am trying to make a sp how looks something like this,
what i want is to be able to get 2tables to create a new customer. .. i dosent work.. can you help me???
@Namn varchar(40),
@Adress varchar(50),
@Postnr varchar(6),
@Beskrivning varchar(max),
@Erbjudande varchar(max),
@kontaktUppgift varchar(30),
@CafeId int
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRAN
declare @CafeId int
BEGIN TRY
INSERT INTO app.Cafe(Namn, Adress, PostNr, Beskrivning, Erbjudande)
VALUES(@Namn, @Adress,@Postnr,@Beskrivning,@Erbjudande)
Insert into app.Kontakt ( kontaktUppgift , CafeID)
values( @kontaktUppgift,@CafeId)
SET @CafeId = SCOPE_IDENTITY();
COMMIT TRAN
END TRY
BEGIN CATCH
–Återställer till innan begin tran
ROLLBACK TRAN
RAISERROR(‘fel i överföringen.’ ,16,1)
END CATCH
END
Tku….
very smart!
how to get identity value of last updated record?
Dave,
I read recently about a bug which could result in scope_identity() returning the wrong value. Do you know if this issue is still outstanding, and if so, should we avoid scope_identity(), or is it possible to mitigate the problem in some way?
The issue is described here:
@@IDENTITY is not working on SQL server 2005.
Why it is not working?
thnks.
@Dnyaneshwar
What is not working about @@IDENTITY? Please give an example of what you are trying, and what you expected.
None of the above is compatible with HSQLDB.
What if we want to junit the MSSQL DAOs using HSQLDB in memory DB?
How can we make the query compatible with both SQLServer and HSQLDB??
I have problem regarding this three
I have created sp for insert values in to three tables. Those three tables are like steps 1st table’s primary key keeps second table and second table primary key keeps the third table. Please follow following example to more clarification
insert Table 1 set ID1=SCOPE_IDENTITY()
Cursor for table 2
Insert table2 (foreign key ID1 ) SET ID2= SCOPE_IDENTITY()
Cursor for table 3
Insert table3 (foreign key ID3 ) SET ID2= SCOPE_IDENTITY()
END CURESOR 3
END CURESOR 2
Can I used Scope_Identity() for this scenario or any other solutions are welcome. Thanks for your advance reply.
Prateek:
Do a loop inside every insert. In that way, you’ll be able to retrieve SCOPE_IDENTITY() after each insert
loop:
begin
Insert 1
loop:
Insert2
loop:
begin
Insert3
end
begin
end
end
Hello All,
I wish to fetch last record from a table in sql server 2005 without using any group by or stored procedure is it possible
hello
would like to know the difference in terminology current session and current scope. Thanks in advance.
@Sachin Kalra
If proc A calls B and B calls C – Everything in all the 3 procedures are in the same session.
However, the SCOPE of a piece of code in PROC C will be limited only to that procedure (C). But it will be in the same session as A and B
Pinal,
This is very good, thank you very much
mathias
Thanks much, Pinal!!