All Forums Database
mnagara 6 posts Joined 11/14
15 Dec 2014
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 :)

Thanks, Mikhil
ulrich 816 posts Joined 09/09
16 Dec 2014

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...)

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

mnagara 6 posts Joined 11/14
16 Dec 2014

Thanks Ulrich :)
 It's working:)
 

Thanks,
Mikhil

You must sign in to leave a comment.