If you're using MS SQL Server 2k you can use an identity column in the related table and use the scope_identity() function to return the value inserted into the column from a stored procedure. That's the most efficient way if you're using SQL Server 2000...
If you're using Oracle or another DB that supports SQL standard sequences, you can do the same thing, but use a sequence instead. Unless you just want to make the foreign key in this other table _also_ the primary key for that table... In which case, you insert the related table after inserting into the primary table. But still use one of the above methods to get the primary key id from the pimary table if it's not known prior to the insert. Otherwise, use createuuid() in your cfml to create a 35 character varchar identity for the record so you know your identity before the insert. The problem with multiple form pages is that they tend to piss users off... they want everything on one page (and why not, I do :) ... The problem with a separate query to get the row number from either the related table or the primary table is that it's contrary to scaling -- the larger your database is, the longer it takes to perform that operation. hth s. isaac dealey 972-490-6624 team macromedia volunteer http://www.macromedia.com/go/team chief architect, tapestry cms http://products.turnkey.to onTap is open source http://www.turnkey.to/ontap > I didn't realize until this project that all of my > relational database > work was kind of one-dimensional, all of the related > tables were built > with dropdowns to feed the main table in mind. None of > the related > tables update information by users. > Now I have a project where I need to add several fields to > a > well-established table in a db. However, only one in 500 > new inserts > would need these fields, so a related (sub)table is the > way to go to save > space. The problem is, not one of my references addresses > an example of > multiple Inserts into a DB. > I've thought of two approaches. Is there a best practice? > (or something > I haven't thought of?) > Action_Form > Query>insert info into new related table > Query>get row number from new related table > Query>pass the row number of the new related table to the > form and Insert > the entire form into main table > all in one shot > or > a series of forms and action pages that kind of step > through the transaction. > Rick > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > ~~~~~~~~~~~| > Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 > Subscription: > http://www.houseoffusion.com/lists.cfm?link=s:4 > Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubsc > ribe.cfm?user=633.558.4 > Get the mailserver that powers this list at > http://www.coolfusion.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Get the mailserver that powers this list at http://www.coolfusion.com
