Get started
Bring yourself up to speed with our introductory content.
Inserting data from a CTE into a file in SQL
To insert data from the CTE into a file in SQL, the common table expression definitions need to be declared within the nested SELECT statement when using a SQL insert statement.
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
Step 2 of 2:
Can I insert data from a CTE into a file? I have this query:
with recursive appidrange ( start, end) as
(select 4500000, max(apptid) from coprddb3/corcvaptp),
allIds (apptidlist, endid) as
( select start as apptidlist, end as endid
from appidrange union all
select existing_row.apptidlist + 1
existing_row.endid
from allIds existing_row
where existing_row.apptidlist +1 <= existing_row.endid),
missingIds as (
select apptidlist from allids
except
select apptid from coprddb3/corcvaptp as temp)
insert into jgriffin/apptids
select * from missingIds;
Since the SQL statement is an INSERT statement, you need to have the common table expression definitions declared with the nested SELECT statement. Here's a simple example:
INSERT INTO out_table
WITH my_cte (m1,m2) AS (SELECT absval(col1), absval(col2) FROM my_table )
SELECT m1, m2 FROM my_cte;
This was last published in June 2009
Dig Deeper on iSeries SQL commands and statements
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our AS/400 experts
View all AS/400 questions and answers
Start the conversation
0 comments