?
Solved

Mass Upload Load a table directly from a cfquery result set.

Posted on 2007-07-30
2
Medium Priority
?
170 Views
Last Modified: 2013-12-16
I have a cfquery result set that I would like to directly update an existing table.

I would like to do this in coldfusion.  Do I output to hidden fields, add an index and then do a cfloop update command?  - Or is there a better way.



<cfquery name="qdirect" Datasource="#Request.datasource#">
SELECT psid,
SUM(CASE labor_date WHEN '01/01/2007' THEN labor_hours ELSE 0 END) AS jan_salary,
SUM(CASE labor_date WHEN '02/01/2007' THEN labor_hours ELSE 0 END) AS feb_salary,
SUM(CASE labor_date WHEN '03/01/2007' THEN labor_hours ELSE 0 END) AS mar_salary,
SUM(CASE labor_date WHEN '04/01/2007' THEN labor_hours ELSE 0 END) AS apr_salary,
SUM(CASE labor_date WHEN '05/01/2007' THEN labor_hours ELSE 0 END) AS may_salary,
SUM(CASE labor_date WHEN '06/01/2007' THEN labor_hours ELSE 0 END) AS jun_salary,
SUM(CASE WHEN (d_i IN ('D','I')
            and proj_def in ('##')
            and activity_type like 'D%'
            and rcc NOT in ('80507'))
      THEN labor_hours ELSE 0 END ) as oTotal
 FROM a_rollup_labor
 GROUP BY psid
 ORDER BY psid
</cfquery>
************


0
Comment
Question by:mahpog
2 Comments
 
LVL 14

Accepted Solution

by:
Scott Bennett earned 2000 total points
ID: 19595158
Assuming the table you want to update has the same columns as your query and already has a row for each of the psid's you can just do something like this:

<cfquery name="qdirect" Datasource="#Request.datasource#">
SELECT psid,
SUM(CASE labor_date WHEN '01/01/2007' THEN labor_hours ELSE 0 END) AS jan_salary,
SUM(CASE labor_date WHEN '02/01/2007' THEN labor_hours ELSE 0 END) AS feb_salary,
SUM(CASE labor_date WHEN '03/01/2007' THEN labor_hours ELSE 0 END) AS mar_salary,
SUM(CASE labor_date WHEN '04/01/2007' THEN labor_hours ELSE 0 END) AS apr_salary,
SUM(CASE labor_date WHEN '05/01/2007' THEN labor_hours ELSE 0 END) AS may_salary,
SUM(CASE labor_date WHEN '06/01/2007' THEN labor_hours ELSE 0 END) AS jun_salary,
SUM(CASE WHEN (d_i IN ('D','I')
            and proj_def in ('##')
            and activity_type like 'D%'
            and rcc NOT in ('80507'))
      THEN labor_hours ELSE 0 END ) as oTotal
 FROM a_rollup_labor
 GROUP BY psid
 ORDER BY psid
</cfquery>

<cfloop query="qdirect">
<cfquery name="UpdateTable" datasource="#Request.datasource#">
UPDATE TableName
SET jan_salary = #qdirect.jan_salary#,
feb_salary = #qdirect.feb_salary#,
mar_salary = #qdirect.mar_salary#,
apr_salary = #qdirect.apr_salary#,
may_salary = #qdirect.may_salary#,
jun_salary = #qdirect.jun_salary#,
oTotal = #qdirect.oTotal#
WHERE PSID = #qdirect.PSID#
</cfquery>
</cfloop>

If you aren't sure if there is an existing record in the table you are updating then you can add some logic to check if there is a record and then decide to udate or insert it like this:

<cfquery name="qdirect" Datasource="#Request.datasource#">
SELECT psid,
SUM(CASE labor_date WHEN '01/01/2007' THEN labor_hours ELSE 0 END) AS jan_salary,
SUM(CASE labor_date WHEN '02/01/2007' THEN labor_hours ELSE 0 END) AS feb_salary,
SUM(CASE labor_date WHEN '03/01/2007' THEN labor_hours ELSE 0 END) AS mar_salary,
SUM(CASE labor_date WHEN '04/01/2007' THEN labor_hours ELSE 0 END) AS apr_salary,
SUM(CASE labor_date WHEN '05/01/2007' THEN labor_hours ELSE 0 END) AS may_salary,
SUM(CASE labor_date WHEN '06/01/2007' THEN labor_hours ELSE 0 END) AS jun_salary,
SUM(CASE WHEN (d_i IN ('D','I')
            and proj_def in ('##')
            and activity_type like 'D%'
            and rcc NOT in ('80507'))
      THEN labor_hours ELSE 0 END ) as oTotal
 FROM a_rollup_labor
 GROUP BY psid
 ORDER BY psid
</cfquery>

<cfloop query="qdirect">
<cfquery name="CheckExists" datasource="#Request.datasource#">
SELECT PSID
FROM TableName
WHERE PSID = #qdirect.PSID#
</cfquery>
<cfif CheckExists.RecordCount eq 0>
      <cfquery name="UpdateTable" datasource="#Request.datasource#">
      INSERT INTO TableName (PSID,jan_salary,feb_salary,mar_salary,apr_salary,may_salary,jun_salary,oTotal)
      VALUES (#qdirect.PSID#,
                  #qdirect.jan_salary#,
                  #qdirect.feb_salary#,
                  #qdirect.mar_salary#,
                  #qdirect.apr_salary#,
                  #qdirect.may_salary#,
                  #qdirect.jun_salary#,
                  #qdirect.oTotal#)
      </cfquery>
<cfelse>
      <cfquery name="UpdateTable" datasource="#Request.datasource#">
      UPDATE TableName
      SET jan_salary = #qdirect.jan_salary#,
      feb_salary = #qdirect.feb_salary#,
      mar_salary = #qdirect.mar_salary#,
      apr_salary = #qdirect.apr_salary#,
      may_salary = #qdirect.may_salary#,
      jun_salary = #qdirect.jun_salary#,
      oTotal = #qdirect.oTotal#
      WHERE PSID = #qdirect.PSID#
      </cfquery>
</cfif>
</cfloop>
0
 

Author Comment

by:mahpog
ID: 19595541
Geez,

I had to get my head out of the one by one insert idea I was thinking in the first round of this task.

My gosh, I should have been drinking on this last request.  

Although, I love your techinque for inserting a new row versus updating.

thx!!!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

When deciding to adopt any help desk solutions many factors should be explored before taking decisions. This will change from business to another but in general there are some kind of rule of thumb. Here are some quick tips: Do we need only ticket…
When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…
Suggested Courses

840 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