Saving 'WITH RECURSIVE' Results

Database
Enthusiast

Saving 'WITH RECURSIVE' Results

So I've written a WITH RECURSIVE query that returns results I like. Now I'd like to get the results of that query into a volatile table, or further process the results.

I know that there's a RECURSIVE VIEW I can use, and that's likely what I'll end up doing. I just prefer volatile tables because I don't have to explicitly drop them at the end of my code (this will be part of a BTEQ that runs daily).

I guess I'm just curious why WITH RECURSIVE is limited to SELECTS. If TD has access to the recursive query in a way that allows it to select the data, why can't it use that data to insert or update a table?
2 REPLIES
Enthusiast

Re: Saving 'WITH RECURSIVE' Results

Hi,

We can insert the output in a table/volatile table.
Please use the sql below to insert in emp1 table.

INSERT INTO db.emp1
(
emp_id,
emp_cd,
level
)
WITH RECURSIVE obj_rec(Level,emp_id, emp_cd)
AS
(
SELECT 1,emp_id, emp_cd
FROM emp
WHERE emp_id =111
UNION ALL
SELECT obj_rec.Level+1,b.emp_id, b.emp_cd
FROM emp b, obj_rec
WHERE b.emp_id = obj_rec.emp_cd
)
SELECT emp_id, emp_cd,Level FROM obj_rec ;

Re: Saving 'WITH RECURSIVE' Results

i want to give output of query as input to the same query