As I posted in my blog entry MSSQL Update/Insert Based Upon Existing Record, it is better to use “IF @@ROWCOUNT=0” rather than “IF EXISTS ()” when updating or inserting a record based upon existence. This is because the preferred method only does a table/index scan once, while the later will do it twice.

What if you need the Identity Column ID whether the record is updated or inserted? Normally in ColdFusion you can use “queryResult[“GENERATEDKEY”]”, however what you will find is this variable only works on simple insert statements. If you try to use @@IDENTITY or SCOPE_IDENTITY, you will find it only returns the Identity Column ID when the insert is triggered.

Introducing MS SQL’s OUTPUT clause, introduced in MS SQL 2005. We can use the OUTPUT clause to return the Identity Column ID(s).

When we add this to the INSERT and UPDATE clauses, the problem we run into is that during an insert it will return an empty set and another set containing the Identity Column ID that was inserted. Therefore we use a temporary table to help us with this.

Now with a temporary table, we introduce back in the second table scan. However this table scan will have very limited rows, if not just one. Plus the temporary table will not need disk access. So the second table scan is of no consequence.

So with all this in mind here’s an example that returns the Identity column that was either inserted or updated. Keep in mind that multiple IDs will be returned as separate rows if more than one table row was updated.

<cfquery name="local.qSetCart">			
	DECLARE @T TABLE (
	    cartID INT NOT NULL
	)
	
	UPDATE
		UserCart
	SET
		dateModified = GETDATE(),
		isGift = <cfqueryparam value="#arguments.isGift#" cfsqltype="cf_sql_bit">
	OUTPUT
		INSERTED.cartID INTO @T
	WHERE
		userID = <cfqueryparam value="#arguments.userID#" cfsqltype="cf_sql_integer">
	
	IF @@ROWCOUNT = 0
	
		INSERT
			INTO UserCart ( userID, isGift)
		OUTPUT
			INSERTED.cartID INTO @T
		VALUES (
			<cfqueryparam value="#arguments.userID#" cfsqltype="cf_sql_integer">,
			<cfqueryparam value="#arguments.isGift#" cfsqltype="cf_sql_bit">
		)
	
	SELECT cartID FROM @T
</cfquery>
		
<cfreturn local.qSetCart.cartID>

#coldfusion-2, #identity, #sql