While developing SQL scripts you'll probably find yourself inserting rows with identity columns at some point.  You'll then probably want to obtain those newly created identity rows (generally ID columns of some sort) and want to insert new rows into other tables using those values.  Give me some C# with LINQ and it'll be done in a minute, unfortunately I had to go the old SQL script route and it's taken me enough time to learn that it's definitely worth documenting.

Here's my script to do so:

DECLARE @NewId1 INT
DECLARE @NewId2 INT

INSERT INTO Table1 (Name) VALUES ('Just a test 1')
SET @NewId1 = (SELECT SCOPE_IDENTITY())

INSERT INTO Table1 (Name) VALUES ('Just a test 2')
SET @NewId2 = (SELECT SCOPE_IDENTITY())

INSERT INTO Table2 (Name, ID1, ID2) VALUES ('Now I can use those new IDs!', @NewId1, @NewId2)

You'll notice here I use the SCOPE_IDENTITY() function which will return the last identity value generated within scope.  This is the most common way to obtain the value, but there are other ways to do it and it's important to understand their differences.  Here is a great article explaining the details:

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

UPDATE:

It's probably better to do an explicit SELECT instead of using the identity field, if possible, because then you're no longer dependant on timing.  For example:

DECLARE @NewId INT
SELECT @NewId = IdColumn1 FROM table1 WHERE uniqueName = 'A Unique Field To Identify the New Row'