Get the Primary ID after a SQL insert using ColdFusion

When you're inserting information in to a database table, it can be very helpful to immediately know what the number in the primary key field, also known as the identity (ID) field, is so you can set up links to editing pages, use it in other database inserts, etc.

Using the code below on MS SQL Server you can retrieve the ID field of the record you have just inserted:

<CFQUERY NAME="DBInsert" DATASOURCE="#Datasource#">
SET NOCOUNT ON

INSERT INTO Address (name,address1,addres2,address3)
VALUES ('#form.name#','#form.address1#','#form.address2#','#form.address3#')
SELECT @@identity AS LatestID

SET NOCOUNT OFF
</CFQUERY>

<CFOUTPUT>#DBInsert.LatestID#</CFOUTPUT>

More code articles

Paul Silver. November 2003