[thelist] @@IDENTITY w/SQL 7 - ColdFusion query

Erik Mattheis gozz at gozz.com
Fri Aug 10 18:15:57 CDT 2001


I'd like to retrieve the primary key ID of an insert statement - SQL 
Server 7/CF 5 ... this was the first thing I tried:

<cfquery datasource="dsn">
    INSERT INTO amazon([columns])
    VALUES([values])
    SELECT @@IDENTITY AS amazon_item_id
</cfquery>

and expected amazon_item_id to be the ID of the row which was 
created, but the value of it appears to be NULL ... which I 
understand to mean there was not a new identity created.

Is my syntax incorrect? Is my table definition incorrect? The primary 
key row, called amazon_item_id is marked as an Identity.

This however works, but seems clunky to me.

<cftransaction>
   <cfquery datasource="dsn">
     INSERT INTO amazon([columns])
     VALUES([values])
   </cfquery>
   <cfquery datasource="april" name="created">
     SELECT @@IDENTITY AS amazon_item_id
   </cfquery>
</cftransaction>

Any ideas or is this what I should be doing?
-- 

- Erik Mattheis

Who reflects too much will accomplish little.

(612) 827 3963




More information about the thelist mailing list