Return the last used identity

IDENT_CURRENT
Returns the last identity value generated for a specified table in any session and any scope.
   IDENT_CURRENT('table_name')

or

SCOPE_IDENTITY
Returns the last IDENTITY value inserted into an IDENTITY column in the same scope. A scope is a module -- a stored procedure, trigger, function, or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch.
   SCOPE_IDENTITY( )

Both descriptions above are from books on line.  Looks like in your case you want SCOPE_IDENTITY immediately after the insert.

fizch, which identity value exactly are you trying to get? If you have more then one user (session) currently performing operations (inserts) on the database, and you want to find out which one the last id was that user A inserted and which one user B inserted, then you HAVE to use @@IDENTITY. SQL Server makes sure that for user A you get the id that was created for the insert performed by/for user A and for user B you get that respective one, even if there inserts where virtually at the same time. If, however, you use any of the other two functions above, you may get another users last inserted id, and thus not the one for the transaction that came logically before. Think of a session as its own independent timeline for each user, where SQL server is going to try its best to make it look like your the only one using the server. SQL Server is going to try even harder if you wrap it in transactions - as a matter of fact, the server makes certain guarantees depending on the transaction level chosen. It's always a safe choice to wrap statements that involve @@identity in an atomic transaction.

From BOL:

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

Here's a little example that within that session will ALWAYS give you the last identity create for the statement above - no matter what other users do and when:

drop table junk
create table junk (
    num int primary key clustered identity(1,1) not null,
    data varchar(2650) not null
)
go

declare
    @lastInsertId int,
    @affectedRows int


begin transaction

    insert into junk ( data ) values ( 'some important data for which I was too busy to determine a natural key' )
    set @lastInsertId = @@identity

    print @lastInsertId

    set @affectedRows = @@rowcount

    print @affectedRows
   
    select @@identity as identityVar

    select @@rowcount as rowcountVar

commit transaction

@@identity and scope_identifier() will both restrict themselves to what YOU do in the current session, so what other users do will not affect you. scope_identifier() will further restrict itself to the current scope. That means the YOU will not interfere with YOU if you want to know the identity that is generated in the insert immediately before the call to scope_identfier(). How could you interfere with yourself? With a trigger. If you have a trigger that inserts another record into the table in question, @@identity will give you the identity created in the trigger, not the one you generated with the insert statement that caused the trigger.

In the following example two identities are generated. One is from the original insert, given by scope_identity(). The other is from the trigger, given by @@identity.

If you want a key to the insert you explicitely did, instead of records from various triggers, scope_identity() is the safe way to go.

drop table junk
create table junk (
    num int primary key clustered identity(1,1) not null,
    data varchar(2650) not null
)
go

CREATE TRIGGER [additionalinsert] ON [dbo].[junk]
FOR INSERT
AS
insert into junk ( data ) values ( 'some more')
GO

declare
    @lastInsertId int,
    @affectedRows int,
    @scopeInsertId int

begin transaction

    insert into junk ( data ) values ( 'some important data for which I was too busy to determine a natural key' )
    set @lastInsertId = @@identity
    set @scopeInsertId = scope_identity()

    print @scopeInsertId
    print @lastInsertId
 
commit transaction

select * from junk

Hi
Simply as this..

DECLARE @IC int
SET @IC = IDENT_CURRENT('<Table Name>')
PRINT @IC

robertjbarker is correct. SCOPE_IDENTITY is the best way to do that. I shouldn't answer questions late at night, sorry about that - no offense meant Robert ;-) Everybody, please ignore my incorrect comment above. Don't use IDENT_CURRENT, though.

From BOL:

SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.