How can I update a table from a query without using form?
i want to be able to update a table cse_montly_reports
this is my code right now
<cfquery datasource="Intranet" name="reports">
with Employee_cte as (
Select e.*,
(Select SUM(r.execoffice_status)
From intranet.dbo.CSEReduxResponses r
where e.emp_id = r.employee
and YEAR(r.approveddate) = YEAR(GETDATE())
and MONTH(r.approveddate) = MONTH(GETDATE())-1
and execoffice_status <> 2
) as TotalStars
From phonelist.dbo.employee e
)
Select Top 10 RANK() Over (Order by TotalStars DESC) AS EmployeeRank,*
From Employee_cte
order by TotalStars desc;
</cfquery>
<cfloop query="reports">
<cfset v_name = reports.emp_namefirst>
<cfset v_total_stars=reports.TotalStars>
<cfquery datasource="Intranet" name="inserdata">
insert into intranet.dbo.cse_monthly_reports
(
starburst_winner
starburst_winner_stars
)
values(
#v_name#
#v_total_stars#
)
</cfquery>
</cloop>
the query works and gets data for me , i have try it on my sql server.
the code above is not inserting into the cse_montly _reports table and is also not giving me any errors, is there a better way to insert into the table
the output from the query looks like something like this(doesnt show all)( and yes i know i can make this query better, but right now im more interested in insert into the other table. what im i doing wrong?
AdChoices