Is there a way to insert collected data into two or more joined table 1

[tatika]

I created a form that collects information from different prepopulated joined tables and stores the information in two other different joined tables.

The source for my insert.cfm is:
<cfinsert datasource=&quot;mydatasource&quot; tablename=&quot;tblTable&quot;

Is there a way to insert the collected data into two or more joined tables?

Hope someone could help.

Thansks in advance

 

[r937]

no

you can insert into only one table at a time

tables are only &quot;joined&quot; in a SELECT query

they may be &quot;related&quot; by vitue of primary and foreign keys, but that's not the same thing

the only way you could work it is via a call to a database stored procedure, but the stored procedure will actually need to be written to insert into one table at a time


rudy

http://r937.com/

 

[tatika]

Rudy:

Would you have any sugestions as far as of coding on how to make the call to the stored procedure? Or could you recomend a site, thread or books to help me with this problem :-(

thanks

 

[r937]

make the call with CFSTOREDPROC

http://livedocs.macromedia.com/coldfusion/5.0/CFML_Reference/Tags98.htm#1103724

as far as writing a stored proc goes, if it's your first time, i suggest you get a book, and which one to get depends on which database you're using (and even then i don't think i can recommend one, because i don't have any)

 

[NoWayIsThisInUse]

Here's one of mine (SQL Server):

CREATE PROCEDURE usp_InsertClientLocation
(@ClientID integer,
@LocationTypeID integer,
@Address1 varchar(200),
@Address2 varchar(200),
@City varchar(60),
@StateID integer,
@Zip varchar(15),
@email varchar(80),
@Phone1 varchar(15),
@fax varchar(15),
@LocationName varchar(50),
@EmergencyHomeDirections text,
@HospitalPhone varchar(15),
@EmergencyLocation bit,
@HospitalRoom varchar(50))
AS

BEGIN TRANSACTION
DECLARE @newLocationID int,
@currentPersonID int

INSERT INTO Location(
LocationTypeID,
Address1,
Address2,
City,
StateID,
Zip,
Email,
Phone1,
Fax,
locationName,
DateCreated,
DateModified,
EmergencyHomeDirections,
HospitalPhone,
EmergencyLocation,
HospitalRoom
)
VALUES(
@LocationTypeID,
@Address1,
@Address2,
@City,
@StateID,
@Zip,
@email,
@Phone1,
@fax,
@LocationName,
getDate(),
getDate(),
@EmergencyHomeDirections,
@HospitalPhone,
@EmergencyLocation,
@HospitalRoom
);
/* DECLARE @NewLocationID = @@identity */
SELECT @currentPersonID = personID
FROM client
WHERE ID = @clientID;

INSERT INTO PersonLocation (
personID,
locationID
)
VALUES(
@currentPersonID,
@@identity
);

COMMIT TRANSACTION

GO

... then of course you execute it in CF

 

[r937]

sweet

great example of a transaction (more than one operation that changes the database, both must succeed or both are rolled back)

also good example of @@identity to create the FK in the child table

thanks for posting that


rudy