Use scope_identity() instead of @@identity

TravisL explains why we should use scope_identity instead of @@identity when we want to get the ID of the last record that was added.

The problem with @@identity is that it returns the ID of the last record that was in the current connection. In which case if the table that you adding to has triggers (which inserts records in other tables) then @@identity returns the ID of the last record that was added by code inside the trigger. This becomes a potentially very difficult bug to track down.

So what should you do? Use scope_identity() which returns the ID of the last record that was inserted in the current scope. See MSDN:scope_identity() for detailed examples.