Alright have to give credit for this post to Andy because he is the one who pointed out how useful the Output Clause on an insert statement could be. Starting in SQL Server 2005 you could add an output clause to an insert, delete, or update statement to return information about the DML you just executed. Below is a simple example that will return the identity inserted into a table in an insert statement without having to issue and @@identity, SCOPE_IDENTITY, or IDENT_CURRENT select
Example:
--Create the table--
create
table dbo.testOutput
(
ident
int identity(1,1) not null primary key
,
otherValue varchar(10) not null
)
-- Run the insert and get back the identity just inserted.-
insert into testOutput
(
otherValue)
output
inserted.ident
values
('Test 1')
The output clause can select from the DELETED and INSERTED tables similar to how you can access the INSERTED and DELETED tables from inside a trigger. BOL has a whole host of information on this command but you might not know to look for it if you didn't know it existed.
Thats it...have a good weekend.