Storing with recursive result set

Database
Enthusiast

Storing with recursive result set

Hi Guys,

i'm using 'With Recursive' to fetch required result set, i need to insert this result set into another table. Please let me know how we can do this.

I tried below syntax, but it's not working, 

with recursive <recursive_name>

(select column list)

as

(

select * from seed table

union all

select * from recurrance table join seed table

)

<insert into Target Table>

select * from recursive table;

Also creating temp table is working but it's taking more time than select & also causing spool space issue,

create table <Target Table>

as

(

with recursive <recursive_name>

(select column list)

as

(

select * from seed table

union all

select * from recurrance table join seed table

)

select * from recursive table;

) with data;

Any help will be appriciated, Thanks in Advance :)

2 REPLIES
Senior Supporter

Re: Storing with recursive result set

You didn't specify the PI in your create table as syntax. 

-> first column is used by default. This can result in many hash synonyms.

-> define a good PI or define the table as NO PI (where I am not sure if you can still end up in skew cases with NO PI and recurisve queries...)

Enthusiast

Re: Storing with recursive result set

Thanks Ulrich :)

 It's working:)