The OUTPUT Clause (Transact-SQL)
While working on a side project, I decided to use VS2K5 and SQL2K5. One of the things I needed to do was take some external data and import it into the database in a normalized manner. I also needed to insert into some additional tables the newly (automatically) created id to link some data. Microsoft SQL Server 2005 (and Microsoft SQL 2005 Express Edition) supports a new TSQL keyword called OUTPUT. Here's basically what it looks like:
INSERT INTO tablename (column1, column2) OUTPUT INSERTED.the_id VALUES (value1, value2)
The OUTPUT INSERTED.[*, column] portion is key here. Upon the row being inserted, the INSERT returns a result with whatever you specified; either a single column or all columns. This allows you to insert data and not have to take an additional step to determine the resulting auto-generated id. You could also use this to return *all* automatically created columns that you didn't pass into the INSERT and were created automatically by having defaulted value for those columns.
more info