asked on

Insert into multiple tables

Good morning,
I have a process where a user can input, edit or delete employee information.It's the example app that comes with CF6 and is working fine.

Here's what I need...the example app creates a UUID that is input into a text/35 length field in an Access employee table. The UUID and insert statement is below.

I have a second table that contains a list of 300 questions

I would like to use that second table, marry it with the UUID and append the (tblemployee.UUID,tble2.*Data into a third table.

TblEmployee has 21 fields and tbl 2 is a series of 300 "questions/survery's" that have to be updated and changed periodically

Can you help?

<cfset NewEmpID = CreateUUID()>

<cfquery name="AddEmployee" dataSource="vianalysis_mdb">
INSERT INTO tblviater (EmployeeID, FirstName, LastName, Email, Phone, Addr1,Addr2,Addr3,City,State,Zip,Comments)
values ('#NewEmpID#',
<cfqueryparam value="#form.FirstName#" CFSQLType="CF_SQL_CHAR">,
<cfqueryparam value="#form.LastName#" CFSQLType="CF_SQL_CHAR">,
<cfqueryparam value="#form.Email#" cfsqltype="CF_SQL_VARCHAR">,
<cfqueryparam value="#form.Phone#" cfsqltype="CF_SQL_VARCHAR">,
<cfqueryparam value="#form.Addr1#" cfsqltype="CF_SQL_VARCHAR">,
<cfqueryparam value="#form.Addr2#" cfsqltype="CF_SQL_VARCHAR">,
<cfqueryparam value="#form.Addr3#" cfsqltype="CF_SQL_VARCHAR">,
<cfqueryparam value="#form.City#" cfsqltype="CF_SQL_VARCHAR">,
<cfqueryparam value="#form.State#" cfsqltype="CF_SQL_VARCHAR">,
<cfqueryparam value="#form.Zip#" cfsqltype="CF_SQL_VARCHAR">,
<cfqueryparam value="#form.Comments#" cfsqltype="CF_SQL_LONGVARCHAR">
)
</cfquery>

anandkp

<CFQUERY dataSource="vianalysis_mdb">
insert into tble2 (employeeid, field1, field2)
values ('#NewEmpID#', 1,1)
</CFQUERY>

& go the same way for table 3 ...

this way - u'll have related records in ur tables ...
or r u looking for something else ?

Larry Brister

ASKER

anandkp,
Table 2 is just a list of questions.
I want to take all of the table 2 questions plus the new UUID and insert all of that into table 3

fields in the tables

table 1 (UUID,First,Last)

table 2 (field1,field2,...)

merger into table 3

table 3 (tbl1.UUID,tbl2.field1,tbl2.field2, tbl2.field(...))

kkhipple

is thsi waht you mean?

<cfset NewEmpID = CreateUUID()>


<CFQUERY dataSource="vianalysis_mdb" NAME="ins_emp">
INSERT INTO table1
VAULES ('#NewEmpID#', '#FirstName#','#LastName#')
</CFQUERY>


<CFQUERY dataSource="vianalysis_mdb" NAME="sel_questions">
SELECT *
FROM table2
</CFQUERY>


<CFQUERY dataSource="vianalysis_mdb">
INSERT INTO table3
VAULES ('#NewEmpID#', '#sel_questions.field1#', '#sel_questions.field2#', '#sel_questions.field(...)#')
</CFQUERY>

Larry Brister

ASKER

kkhipple,

That worked...partially.

It inserts the new record into table 1

But table two (the merge table) has 267 records in it that have to all go into table three with the NewEmpID on each row.

It's only inserting the top record.

Should I have some kind of cfloop statement?

Thanks,

ASKER CERTIFIED SOLUTION

kkhipple

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Larry Brister

ASKER

kkhipple,
You put me on the right track. Thanks. After merging your information with other cfloop questions I came up with the winner.
Points being awarded now...

This is the code.



<cfquery name="get_questions" datasource="vianalysis_mdb">
SELECT *
FROM Test_Questions
</cfquery>

<cfset Start=1>
<cfset End=267>
<cfloop query="get_questions"
startrow="#Start#"
endrow="#End#">
<cfquery name="AddQuestions" dataSource="vianalysis_mdb">
INSERT INTO test_input (EmployeeID, Question_ID, Test_ID, Sort_Order, Type, Question)
      values ('#NewEmpID#','#get_questions.Question_ID#',
      '#get_questions.Test_ID#','#get_questions.Sort_Order#',
      '#get_questions.Type#','#get_questions.Question#')
</cfquery>
</cfloop>