How to insert records using select statement?
WITH temp_mytbl AS ( -- without aggregation in output expression / HAVING clause
                     -- GROUP BY must be replaced with DISTINCT
                     -- which is less expensive
                     SELECT DISTINCT iex_id, dte, agent_name, schd_total
                     FROM source_xrx_iex6_sandy_1.iex6_agent_adh_sum 
                   )
SELECT i.dte, idm.empl_id, 1 AS entity_id, SUM(i.schd_total) AS s_schdhours
FROM temp_mytbl i
JOIN source_odw_usac.t_incontact_agentstatelog a ON a.winid = i.iex_id::text
JOIN reporting_prod.idm_dte idm ON idm.src_id = a.agent_no 
                               AND idm.src_type_id = 618 
                               AND soar_date(a.calendar_date_key) &gt;= idm.start_dte 
                               AND soar_date(a.calendar_date_key) &lt;= COALESCE(idm.end_dte, now()::date)
GROUP BY i.dte, idm.empl_id /* , entity_id */ 
-- LIMIT without ORDER BY makes no sense
ORDER BY i.dte /* or another sorting expression */
LIMIT 100;

or the same in subquery form
SELECT i.dte, idm.empl_id, 1 AS entity_id, SUM(i.schd_total) AS s_schdhours
FROM ( SELECT DISTINCT iex_id, dte, agent_name, schd_total
       FROM source_xrx_iex6_sandy_1.iex6_agent_adh_sum 
     ) i
JOIN source_odw_usac.t_incontact_agentstatelog a ON a.winid = i.iex_id::text
JOIN reporting_prod.idm_dte idm ON idm.src_id = a.agent_no 
                               AND idm.src_type_id = 618 
                               AND soar_date(a.calendar_date_key) &gt;= idm.start_dte 
                               AND soar_date(a.calendar_date_key) &lt;= COALESCE(idm.end_dte, now()::date)
GROUP BY i.dte, idm.empl_id 
ORDER BY i.dte
LIMIT 100;