Strictly speaking, isn't that a bit risky? You actually have 2 commands in there, and I don't think they are treated as a unit transaction, are they? So another process could slip in there between those two on you, and you *might* (unlikely, but...) get back the wrong ID.
If you're using SQL Server, you should use the @@identity feature, which is guaranteed to be correct within your connection (in this case, with CF's connection to the database), and therefore won't risk being clobbered by another operation elsewhere. Just a thought. :P -----Original Message----- From: Ray Thompson [mailto:[EMAIL PROTECTED] Sent: Monday, September 15, 2003 15:23 To: SQL Subject: RE: insert/select I just did this on SQL Server 2K. Always gets back the highest row inserted. <cfquery name="Test" datasource="#Request.ModeParms.dbMember#"> insert into TestTable (TheStuff) values ('Test Data'); select * from TestTable where ID in (select max(id) from TestTable) </cfquery> <cfdump var="#Test#"> Table only has two columns, the ID (auto increment) and a text field (TheStuff). Ray Thompson Systems Administrator Tau Beta Pi Association www.tbp.org The Engineering Honor Society Integrity and Excellence in Engineering > -----Original Message----- > From: Michael Dinowitz [mailto:[EMAIL PROTECTED] > Sent: Monday, September 15, 2003 4:07 PM > To: SQL > Subject: insert/select > > > Is there a way of doing an insert and a select on the data > that was just inserted as a single operation inside a CFQUERY > tag? Not in an SP, but as straight SQL. If not, I'll have to > do the transaction, insert, select thing. I'm just looking > for the best option. Thanks > > Michael Dinowitz > Finding technical solutions to the problems you didn't know > you had yet > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:6 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=<:emailid:>.<:userid:>.<:listid:> Get the mailserver that powers this list at http://www.coolfusion.com
