And for those folks using MSSQL, please note that @@IDENTITY is probably not what you want either. @@IDENTITY returns the last identity produced by a connection to the database, regardless of the table. So in some circumstances you can end up with an identity returned that is not from the table you just inserted into. In most cases you'll instead want to use SCOPE_IDENTITY() which returns the identity produced by the current connection and current statement combination.
For more, you can read up here: http://bit.ly/rhkvf Cheers, Judah On Tue, Sep 29, 2009 at 12:59 PM, Dave Watts <[email protected]> wrote: > >> You can also do this using SQL. Sure this works with MS SQL, not 100% sure >> about mySQL but it's standard SQL, so it should work. >> >> <cfquery name="putData" datasource="#dsn#"> >> SET NOCOUNT ON >> INSERT INTO my_table (my_field1, my_field2) >> VALUES ('#trim(value_field1)#', ('#trim(value_field2)#') >> SELECT @@Identity AS newId >> SET NOCOUNT OFF ; >> </cfquery> >> >> <cfoutput>The Generated ID is: #putData.newId#</cfoutput> > > The @@IDENTITY global variable is not standard SQL. It's specific to > T-SQL. So is SET NOCOUNT ON|OFF. Of the four SQL statements you've > listed, one is standard SQL. > > Dave Watts, CTO, Fig Leaf Software > http://www.figleaf.com/ > > Fig Leaf Software provides the highest caliber vendor-authorized > instruction at our training centers in Washington DC, Atlanta, > Chicago, Baltimore, Northern Virginia, or on-site at your location. > Visit http://training.figleaf.com/ for > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326758 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
