Q

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.

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;
As a result of this query, I get an SQL code of: -199 Insert not expected. How can I do this?
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 first 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.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchEnterpriseLinux

SearchDataCenter

Close