How to insert records using select statement?

Question

Hi I am working on one project, here what I am trying is, I want to insert records what I get using select statements into temp table. but it is giving error to me, can any one help me to solve this ?

ERROR: relation "temp_mytbl" does not exist SQL state: 42P01

following is my query

truncate table temp_mytbl;
create temporary table temp_mytbl as
select iex_id,dte,agent_name,schd_total
FROM source_xrx_iex6_sandy_1.iex6_agent_adh_sum limit 0;
 insert into temp_mytbl
select iex_id,dte,agent_name,schd_total
FROM source_xrx_iex6_sandy_1.iex6_agent_adh_sum 
group by iex_id,dte,agent_name,schd_total;
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) >= idm.start_dte AND soar_date(a.calendar_date_key) <= COALESCE(idm.end_dte, now()::date)
 GROUP BY i.dte, idm.empl_id limit 100;

• Akina: but it is giving error to me What statement generates the error? What is the error message generated by DB server precisely? Add DDLs for all 3 tables used into the question text.
• humberto herrara: syntax error near select
• Akina: Please post the whole error message without any edition storing linebreaks, spaces counts and so on...
• Akina: This error message means that the table/view temp_mytbl not exists in your database. So you must create it before use.
• Akina: Remove TRUNCATE statement. And use one CREATE TABLE .. SELECT instead of 2 separate queries. PS. Your intermediate static table seems to be excess - use CTE.

Answer 1

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) >= idm.start_dte 
 AND soar_date(a.calendar_date_key) <= 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) >= idm.start_dte 
 AND soar_date(a.calendar_date_key) <= COALESCE(idm.end_dte, now()::date)
GROUP BY i.dte, idm.empl_id 
ORDER BY i.dte
LIMIT 100;

• humberto herrara: hi akina, I appriciate your effort for this, it is helpful. this query is working fine. but using distinct it gives data for same id. what I was working to get the data in temp table, can you help me with group by ?
• humberto herrara: can you help me with this issue dba.stackexchange.com/questions/270764/…

Answer 2

create table #temp_mytbl (
 iex_id int,
 dte int,
 agent_name varchar(10),
 schd_total float
 )
 insert into #temp_mytbl
select iex_id,dte,agent_name,schd_total
FROM source_xrx_iex6_sandy_1.iex6_agent_adh_sum 
group by iex_id,dte,agent_name,schd_total;
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) >= idm.start_dte AND soar_date(a.calendar_date_key) <= COALESCE(idm.end_dte, now()::date)
 GROUP BY i.dte, idm.empl_id limit 100;
 DROP TABLE #temp_mytbl

• user1822: #temp_mytbl is an invalid identifier in Postgres (and standard SQL)