There’s a handy little query you can run in SQL to retrieve the identity (primary key) value of a record you just created. So, you can do something like this:

INSERT INTO
  People (
    Name
  ) VALUES (
    'Joe'
  );
  SELECT @@IDENTITY AS NewName;

This query will create a new person and also return the identity value of the newly created record. It can be useful for all kinds of things – I use it frequently.

We recently upgraded to ColdFusion 8 here at Purdue, and my code started breaking.

It turns out that CF8 attempts to “help” you by automatically running SELECT @@IDENTITY after any INSERT query. This is problematic in two ways – it cancels out your own @@IDENTITY query, and it arbitrarily names the result Generated_Keys.

So instead of being able to do something like this:

<cfset TheNewName = People.NewName>

You must use this:

<cfset TheNewName = People.Generated_Keys>

It’s really more annoying than anything else. It seems like a good idea to include the code automatically, but I think it would probably be a little better if there were a conditional in there that prevents CF8 from inserting its automatic query if a SELECT @@IDENTITY query is already present.