Just to present another option, SQL 2008+ also supports MERGE for "upserts". Adding an OUTPUT clause will provide access to the special $action
variable. As the name implies, it will indicate the action actually performed ("insert" or "update").
<cfquery name="qryTemp" datasource="#someDSN#">
MERGE INTO tempTable tmp
USING ( VALUES ( 1, 2, 3 ))
AS data (someID, colA, colB)
ON data.someID = tmp.someID
WHEN MATCHED THEN
UPDATE SET tmp.ColA = data.ColA
, tmp.ColB = data.ColB
WHEN NOT MATCHED THEN
INSERT (someID, colA, colB)
VALUES (data.someID, data.colA, data.colB)
OUTPUT inserted.someID AS ModifiedID
, $action AS Action;
</cfquery>
<!--- Demo: Was an insert or update peformed? --->
<cfif qryTemp.Action eq "INSERT">
ID inserted = <cfoutput>#qryTemp.ModifiedID#</cfoutput>
<cfelse>
ID updated = <cfoutput>#qryTemp.ModifiedID#</cfoutput>
</cfif>
NB: Though out of the box, concurrency is still an issue with either methods.