Getting the ID field after an Insert
About every 2 months or so someone asks about reliably returning the primary key record from an insert query. The problem they are trying to solve usually has to do with additional inserts into related tables. For example, if you are adding a new user and you want to set group permissions as well - but group permissions require inserting into another table. One way to do it is to do an insert, then do a second query that pulls back the "max(id)" and uses another qualifier - like an email address. This requires 2 connections to the database, but it is a very common method. If you are using SQL server and your primary key is an "identity" type field then you have another option. You can insert and get back the identity value in the same query. This is preferred because of SQL treats a single query statement as an implicit transaction - meaning you are assured of data integrity, and that you will return the right value. Here's the way it works.
SET NOCOUNT ON
INSERT INTO USERS (username, email)
VALUES
(<cfqueryparam cfsqltype="CF_SQL_CHAR" value="#usersname#">,
<cfqueryparam cfsqltype="CF_SQL_CHAR" value="#email#"> )
SELECT @@Identity AS newId
SET NOCOUNT OFF
</cfquery>
If you output the value of "#putUser.newId#" you will see it's value is the new value of the "identity" field for the record you just created. Notice also the "NOCOUNT" attribute that I toggle on and off. This is to prevent some "extra" data from being sent back to the driver during the operation. If you've ever run multiple statements in query analyzer you will have seen this extra data in the message pane. It's the data that says "1 row affected" or "3 rows affected". SQL keeps track of the number of rows and outputs data to the driver telling it what is happening. This used to cause exceptions to be thrown on ODBC drivers when trying to access server level vars like @@Identity. Setting NOCOUNT ON and back OFF again alleviates this issue. I have to say candidly that I have no idea if this is an issue on a CFMX server having never tested it. Either way, setting nocount on and off makes a multi-statement query faster and more efficient.
You might be wondering about the variable @@identity. The variable @@identity contains the last identity value generated in the current session(the current session meaning "this" connection). That means if you do 2 inserts to 2 different tables and then select @@identity (which you must alias by the way), you will get the identity value of the last statement. Just be careful about how you arrange your statements and be cautious about updating more than 1 table in single statement. If you need to manipulate multiple table identity fields in a single statement the SCOPE_IDENTITY() is a better choice.
One more word of caution - watch out for triggers! A common use of triggers is to insert into table B when an insert is done on table A. For example, suppose you had a log table that contained the create date for inserting a new user and you put an "insert" trigger on the users table that inserted the userid and getDate(). If that second "log" table has an Identity field, @@identity will return the ID of the log table - since it was the "last identity generated in the session." In that case your only option is to use the SCOPE_IDENTITY() function as in: SELECT SCOPE_IDENTITY() AS newID.
7 Comments
-
Hi Mike,
Why should anyone *ever* use @@Identity when SCOPE_IDENTITY() is available? I've sort of banned it from my projects. -
Joe - I understand the sentiment. Many - perhaps most - of the folks who need this advice aren't using triggers or sql statements with multiple blocks. They are "just" trying to do exactly as described and get back the id field from a single insert. This basic approach in my opinion is fine for them. Using scope_identity() in a multiple select statement can have it's own pitfalls and it's harder to grasp the idea of "scope" in an SQL statement. Having said all of that - I largely agree with you as a matter of coding practice on very SQL intesive projects. -
Hi Mike, Thanks for the post, you have save my day.
I have just update the CF server with the 6.1 updater and the new DataDirect JDBC drivers 3.5 and suddenly the MSsql does not return the ID from the table (from TRIGGER) and I try hours to find out why. Finally I read your post here and try putting the SET NOCOUNT ON/OFF between the cfquery tag and it works! (Not sure why). But next time I will sure to use your method to return the Identity, it is much more easy to troubleshoot. -
Something I've been pretty excited about is the new OUTPUT clause in SQL Server 2005. We recently migrated to 2005, so I've been playing with it and it works great, solves a lot of headaches for me. You can also return a usable recordset with DELETE, INSERT, and UPDATE statements.
Using the above example, you could re-write it like this:
<cfquery name="putUser" datasource="#dsn#">
INSERT INTO USERS (username, email)
OUTPUT INSERTED.UserID [newID]
VALUES
(<cfqueryparam cfsqltype="CF_SQL_CHAR" value="#usersname#">,
<cfqueryparam cfsqltype="CF_SQL_CHAR" value="#email#"> )
</cfquery>
Would return a recordset with a single column, newID, that would hold the inserted identity value. -
thanks for your useful link
I have a problem with @@Identity and after reading your post and using "SET NOCOUNT ON"
I goes away....
thanks -
In case of openbd this number is captured in the result structure as GENERATED_KEY. It doesn't matter what DBMS you have used. That is abstracted out from the user.
Sites to Watch
Aggregators
RSS
