MS SQL 2005: The under-used OUTPUT clause

While not new, some of the nicer features first introduced with MS SQL 2005 are often overlooked. After spending time working with different, or older, databases I sometimes forget about them myself. One of the more useful features worth remembering is the OUTPUT clause.

If you are not familiar with it, OUTPUT clause allows you to retrieve information about records that were just inserted, updated or deleted. This information can be returned as resultset or even inserted into another table (or table variable). It is incredibly useful when working multiple records, but comes in handy in other situations as well.

result.IDENTITYCOL alternative
One example is with INSERT statements. While ColdFusion 8 introduced the much needed result attribute (which can return the ID of a newly inserted record) it has a few limitations. It only works for single record inserts, and also has a few quirks. The OUTPUT clause offers an interesting alternative to using CF 8's result.IDENTITYCOL value.

Take the simple insert below that adds a record into a table with an identity column. By adding an OUTPUT clause, and using the special Inserted prefix, you can access the id of the newly created record (or any other value in the record).

By default, OUTPUT returns the information as resultset. So you end up with a query containing the new record ID. Just as if you were using a SELECT statement instead. On the "upside", this seems to work consistently with the CF 8, MS SQL JDBC 1.0 and 1.2 drivers, unlike the result attribute. Unfortunately ... it did not work with the jtds driver. At least not in my tests. Still an interesting alternative to result.IDENTITYCOL.

Using OUTPUT

CREATE TABLE Customer
(
CustomerID INT IDENTITY(1,1),
FirstName VARCHAR(50),
LastName VARCHAR(50)
)

<cfquery name="addRecord" datasource="#dsn#">
INSERT INTO Customer ( FirstName, LastName )
OUTPUT  Inserted.CustomerID
VALUES
(
   <cfqueryparam value="Alan" cfsqltype="cf_sql_varchar"> ,
   <cfqueryparam value="Mercury" cfsqltype="cf_sql_varchar">
)
</cfquery>

<cfdump var="#addRecord#" label="Show the new Record ID">

One of these days I will have chart out all of the options, the various issues with each, and see which one equates to the least headaches.

* Improving inserts, updates and deletes with OUTPUT