The question is "How to fetch the IDENTITY value of the record which we INSERTED now?". Till the days of SQL Server 7.0 we used to rely on @@IDENTITY function. But in my experience that function isn't that reliable. i.e., @@IDENTITY isn't dependent on the current scope. Even if we have inserted some records in a different table it would fetch us that identity value ;) I am sure we wouldn't be interested in that.
From SQL 2K there is a new function by name SCOPE_IDENTITY which returns the last IDENTITY value produced on a connection and by a statement in the same scope. So its better to use SCOPE_IDENTITY in our select statement to retrieve the identity value for the record which we inserted now.
Example: SELECT SCOPE_IDENTITY()