Using the SQL Server Output Clause

Capturing Inserted IDs

The example below shows how to caputer inserted IDs using the output clause. This assumes there is a table in the database named FirstNames that has just an autoincrement ID column and a "FirstName" column.

                  DECLARE @TempTableToHoldIDs table( ID int,
                           FirstName varchar(50));
                           
                  INSERT FirstNames
                  OUTPUT INSERTED.ID, INSERTED.FirstName
                    INTO @TempTableToHoldIDs
                    VALUES ('Bill');
                    
                  Select * from @TempTableToHoldIDs
                                              
                  --Returns 1 (or whatever the inserted ID is) and billBill          
            

Handling an Update

This is great if you want to keep a history of changes made to a table. With a little bit of work you can chage the temp table used below into a permanent table in the database to track the changes made to your table. This is helpful in case you ever need to rewind data.

                 DECLARE @TempTableToHoldIDs table( ID int,
                           DeletedFirstName varchar(50),
                           InsertedFirstName varchar(50));
                           
                 UPDATE FirstNames
                 SET FirstName = 'Billy'
                 OUTPUT INSERTED.ID,
                   DELETED.FirstName,
                   INSERTED.FirstName,
                 INTO @TempTableToHoldIDs
                 WHERE FirstName = 'Bill'
                 
                 Select * from @TempTableToHoldIDs
                 
                 --Returns 1, Bill, Billy
             

Handling a Delete

It works pretty much like the insert. Except use the Deleted clause instead of the inserted. You can see how it works inthe Update example. I don't use this too often, it's easy to tell if your delete was successful. And it's kind of rare that you want to save something you delete.