CODEWITHSUNDEEP
sqloracle10gsql-insert
Hey StackExchange
Hey StackExchange

Reputation: 2125

Oracle 10g: Inserting multiple rows

I'm having the following select statement :

select dte, wm_concat(issue) as issues
from ((select date_a as dte, issue from t where date_a is not null) union all
      (select date_b, issue from t where date_b is not null)
     ) di
group by dte
order by dte;

that returns multiple rows such as:

DTE        | ISSUES
-----------+---------
01/JUN/91  | EE
01/JUN/03  | EE
01/JAN/06  | HH
01/AUG/06  | EE
01/AUG/08  | EE,HS,HE

I would like insert these records into a table.

Question

How should I write the insert statement ? Should I use a cursor as it seems INSERT can process one row at time ?

Upvotes: 0

Views: 86

Answers (1)

user330315
user330315

Reputation:

Use the select as the source for the insert:

insert into some_table (dte, issues)
select dte, wm_concat(issue) as issues
from (
   select date_a as dte, issue 
   from t 
   where date_a is not null
   union all
   select date_b, issue 
   from t 
   where date_b is not null
) di
group by dte;

There is no need to put the individual queries of a UNION between parentheses.

Upvotes: 2

Related Questions