Get the last inserted row ID with SQL statement

Question

When you add a new record to the table, please send me the newly formed ID.

This is what I read: MSDN: http://msdn.microsoft.com/en-us/library/ms177564.aspx however a temporary table must be created. After running the INSERT statement, I want to return the ID (assuming executing just one INSERT).

Example:

1     Joe      Joe
2     Michael  Mike
3     Zoe      Zoe

I want to return the newly created ID, which equals 4 while running an INSERT statement. Could you please tell me how to accomplish it using a SQL statement, or is it not possible?