Get the id of a newly created record

Need to get the id of the record you just created? Don't want to run a second query to get it? Well, this works with SQL Server, I don't know about any others...


<cfquery name="InsertInfo" datasource="DSN">
SET NOCOUNT ON

DECLARE Purchase_ID int -- optional line. Caused an error for me once. Removed it, query worked.

INSERT INTO tbl_Purchases
(
Purchase_Details, Purchase_Date
)
VALUES (
'#FORM.OrderDetails#', #Now()#
)

SELECT @@IDENTITY as Purchase_ID

SET NOCOUNT OFF
</cfquery>

<cfset messageid="#GetNewMessageID.MessageID#">