Not logged in - Login

Using an OUTPUT clause with an INSERT statement

The following is an example of using the OUTPUT clause with an INSERT statement.

This can be useful when it is necessary to know the ID values (identity or unique identifiers) that were inserted into a table. 

/*
CREATE table Table1
(
   Id int not null identity
   , TextInfo1 varchar(15)
   , IntInfo2 int
   , uuid uniqueidentifier
)
*/

DECLARE @TableIDs table (
   ID int
   , uuid uniqueidentifier
)

INSERT into Table1 (TextInfo1, IntInfo2, uuid )
output inserted.Id as ID, inserted.uuid as uuid into @TableIDs (ID, uuid)
values 
   ('hi there', 5, newid())
   , ('hello world', 8, newid())

-- output our table of ID values.
select * from @TableIDs

The sample above will only run on SQL2008 or higher due to the structure of the 'values' clause in the insert, but the OUTPUT clause was available from at least SQL2005.

Note that this technique cannot be used to output any column values from the source table (if the source for the insert is a SELECT statement.) If this is required, you will need to use the OUTPUT clause with the MERGE statement.