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?
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 ;