Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Insert into multiple tables

Posted on 2004-10-07
6
192 Views
Last Modified: 2013-12-24
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>

0
Comment
Question by:lrbrister
  • 3
  • 2
6 Comments
 
LVL 17

Expert Comment

by:anandkp
ID: 12248500
<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 ?
0
 

Author Comment

by:lrbrister
ID: 12248600
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(...))
0
 
LVL 5

Expert Comment

by:kkhipple
ID: 12249093
is thsi waht you mean?


<cfset NewEmpID = CreateUUID()>

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

<!--- grab all questions --->
<CFQUERY dataSource="vianalysis_mdb" NAME="sel_questions">
     SELECT *
     FROM table2  
</CFQUERY>

<!--- merge into table 3 --->
<CFQUERY dataSource="vianalysis_mdb">
     INSERT INTO table3  
      VAULES ('#NewEmpID#', '#sel_questions.field1#', '#sel_questions.field2#', '#sel_questions.field(...)#')
</CFQUERY>
0
Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

 

Author Comment

by:lrbrister
ID: 12261199
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,
0
 
LVL 5

Accepted Solution

by:
kkhipple earned 500 total points
ID: 12263217
yes. you'll need a loop


try this ----


<!--- merge into table 3 --->
<CFLOOP QUERY="sel_questions">
    <CFQUERY dataSource="vianalysis_mdb">
      INSERT INTO table3  
        VAULES    
('#NewEmpID#',  '#sel_questions.field1#',  '#sel_questions.field2#',  '#sel_questions.field(...)#')
     </CFQUERY>
</CFLOOP
0
 

Author Comment

by:lrbrister
ID: 12266405
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.

<!--Other queries first...........-->

<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>
0

Featured Post

Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question