Hi Rick,
If I understand you correctly your sub table and main table need to be
linked by a shared column or columns so that it is possible to find the
row in the sub table that relates to the row in the main table.
The first step is to establish what this will be. If (as your post
suggests) there is a one to one relationship, you could use the unique
key of the main table in the sub table to form the relationship. (In the
sub table this is known as the foreign key).
>From then on things are fairly simple.
Assuming you have gathered all the column data from form fields or
wherever you can insert into both tables in one transaction as follows
Actionpage
<cftransaction>
<cfquery name="insMainTable" ....>
insert into mainTable
(uniqueKey,.......)
Values
(#uniqueKey#,.....)
</cfquery>
<cfif the extra fields for sub table exist>
<cfquery name="insSubTable" ....>
insert into subTable
(uniqueKey,.......)
Values
(#uniqueKey#,.....)
</cfquery>
</cfif>
</cftransaction>
You will probably want to add a try and catch for error handling.
HTH
Michael Traher
Systems Manager
ICLP (London)
-----Original Message-----
From: Rick Kennerly [mailto:[EMAIL PROTECTED]
Sent: 02 September 2003 13:54
To: CF-Talk
Subject: Mutiple SQL Inserts
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/unsubscribe.cfm?user=89.70.4
Get the mailserver that powers this list at
http://www.coolfusion.com
