What is SCOPE_IDENTITY() for then? -----Original Message----- From: Ayudh Nagara [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 November 2003 2:44 PM To: CFAussie Mailing List Subject: [cfaussie] RE: get primary key allocated during insert?
@@IDENTITY is connection-specific. It returns the last identity value created on the same database connection. So if you retrieve the @@IDENTITY within the same CFQUERY as the INSERT statement, you can be assured it will return the identity you're after. SQL Server only AFAIK. <CFQUERY NAME="NewCustomer" ....> INSERT INTO customers (..., ..., ...) VALUES (..., ..., ...); SELECT @@IDENTITY AS NewCustomerID FROM customers </CFQUERY> Regards: Ayudh +----------------------------------------------------------------+ | SOAP is the glue! Hook up your server directly to your bank. | | Connect to VeriPay xServ, the Australian Payments Web Service. | | Reliable, Secure, FAST: http://www.xilo.com/xserv | +----------------------------------------------------------------+ ----- Original Message ----- From: "Taco Fleur" <[EMAIL PROTECTED]> To: "CFAussie Mailing List" <[EMAIL PROTECTED]> Sent: Wednesday, November 26, 2003 10:20 AM Subject: [cfaussie] RE: get primary key allocated during insert? @@IDENTITY returns the last ID inserted globally, it does not guarantee that it is actually the ID your after. SCOPE_IDENTIY() returns the last ID within the scope. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 November 2003 9:16 AM To: CFAussie Mailing List Subject: [cfaussie] RE: get primary key allocated during insert? Brian, I use this query immediately following an insert query if I want the primary key value: <CFQUERY NAME="getid" DATASOURCE="dsn"> SELECT @@Identity as artid FROM tablename </cfquery> I guess you could wrap the two queries in a CFTRANSACTION if it is high traffic. Regards... Steve C -----Original Message----- From: Brian Gilbert [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 November 2003 10:12 AM To: CFAussie Mailing List Subject: [cfaussie] get primary key allocated during insert? Hi all, Is there a way to get the primary key (autonumber) that would be allocated during an insert statement in a cfquery? TIA Brian --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004 --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004 --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004 --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004 --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004
