Best solution to SQL insert-retrieve query function. (Tacobell777 - but others too!)
Posted on 2004-09-29
Been looking forever for a better solution to this than wrapping two queries into a transaction in order to retrieve the new record ID of a row that was just inserted by the query. Don't want to have to write stored procs for every query I use and hate having to hit SQL twice to just get my new records auto-increment ID value. This is primarily for TacoBell777, but if anyone else has any really nifty <CFQUERY> tricks or tips here I'll possibly split some points their way too!
BTW - for easy searching by others and for my own referrence, TacoBell777 if you could put a complete example of a short insert-retrieve function here in addition to your other example I'd love it! Lets say that table TBLMYDATA has the following columns:
RowID (INT, AUTO, PK)
Thanks again for the cool tip and for understanding SQL much better than I do. :)