@@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT

@@IDENTITY returns the id of the last thing that was inserted by your client's connection to the database. Most of the time this works fine, but sometimes a trigger will go and insert a new row that you don't know about, and you'll get the ID from this new row, instead of the one you want
SCOPE_IDENTITY() solves this problem. It returns the id of the last thing that you inserted in the SQL code you sent to the database. If triggers go and create extra rows, they won't cause the wrong value to get returned.
IDENT_CURRENT returns the last ID that was inserted by anyone. If some other app happens to insert another row at an unforunate time, you'll get the ID of that row instead of your one.




Following code nicely work with Sql server 2005

DECLARE @last_id INT;

INSERT INTO [my_table] ([col1],[col2])
OUTPUT INSERTED.[id] INTO @last_id
VALUES (@col1,@col2)




More Info:
http://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row