Identity Field

Folks

I am inserting some values into a table with the following stmt

Insert into table(number,name) values ('12','name')

In the table I have one more identity column ID. I know that I cannot insert a value in that column and the value is automatically increased once I insert a record. After this insert statment, I need to get the value
of the ID (the most recent one) in the next select statement.

ie Select @.@.identity from table (any condition??)

How do I get the most recent ID value? Actually I m inserting the records in a loop and the ID is increased for every insert.

Thanks for the help,There is only one @.@.identity tracked for any given connection to MS-SQL (each spid). To retrieve its value, you just select it (no table needed). Something like:DECLARE @.id INT
INSERT INTO HHGtable (theAnswer) VALUES (43) // whatever
SELECT @.id = @.@.identity-PatP|||Pat

Thanks for the idea. BTW I have a question can I use

select Max(ID) from table

So that It gives only the maximum value and it would be same value
when the record is inserted? I am just asking your suggestion. Is that logically correct??

Thanks for the help,|||I would think so, but only as long as the tables next identity never is reset to a lower value (dbcc checkident is able to do so).