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

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


mahpogAsked:
Who is Participating?
 
Scott BennettConnect With a Mentor Manager TechnologyCommented:
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
 
mahpogAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.