Inserting sql query into temp table for later use

Question

I have a table which I need to perform actions on and then insert the result in a temp table so I can join the temp table with other tables later. Here's my code:

if object_id ('tempdb..tempMHR_X') is not null
drop table tempMHR_X;

create table tempMHR_X (
tempTM_LINE_ITEM_SA_ID int,
tempMH_X int,
tempCHRG_DT_MONTH int,
tempCHRG_DT_YEAR int
)

insert into tempMHR_X (tempTM_LINE_ITEM_SA_ID, tempMH_X, tempCHRG_DT_MONTH, tempCHRG_DT_YEAR)
select tc.TM_LINE_ITEM_SA_ID, sum(tc.MH_X), extract(month from tc.CHRG_DT), extract(year from tc.CHRG_DT)
from TM_CHARGE tc
group by tc.TM_LINE_ITEM_SA_ID, extract(month from tc.CHRG_DT), extract(year from tc.CHRG_DT)

I am getting an 'ORA-00922: Missing or invalid operation' error on the insert statement. I have tested everything from the beginning of the select statement down and it returned the correct result. Now, I just want get that result into the temp table tempMH_X so I can join the temp table with other tables.

FYI, I am using an Oracle 10g database. I have tried using the '#' sign before my temp table name, but TOAD says that is an invalid operator. Any help is much appreciated. Thank you!

James

If your select query is working, change it so that it is selecting only one field. Try to insert that field into your temp table. If you succeed, add a second field. Keep going until you find the error or finish the job. — Dan Bracuk
Are you missing a semicolon after your create table statement? — Abdulgood89

Yuri G Yuri G · Accepted Answer · 2017-01-30T22:17:55

0

votes

Missing semicolon after CREATE TABLE?

Inserting sql query into temp table for later use

1 Answers