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:
1
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.