How to transfer a DB2 SQL result table to a physical file?

Question

I would like to get the result table from the SELECT , and then move it to a physical file.

I was hoping to use (behind W3Schools ):

 SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_tablename 

Unfortunately, for the IBM manual (DB2 link for I am SQL)

The SELECT INTO creates a result table consisting of one row itself and assigns values ​​in this row to variables.

So, I thought, maybe I could use:

The INSERT inserts rows into a table or view. There are three forms of this statement:
1. The INSERT form using the fullselect form is used to insert one or more rows into a table or view using values ​​from other tables or views.

Unfortunately, this does not work for me.

 INSERT INTO <tablename> FROM ( SELECT (*) FROM <querytableA> UNION SELECT (*) FROM <querytableB>) 

I can move the result table to a physical file, but for this I need to declare a cursor, and then with do … while write to the physical file one record at a time. I find this ugly and inelegant and was hoping to use a SELECT INTO or INSERT INTO .

Please help me “upload” the results table to a physical file using one of the elegant statements.

OS: IBM i v6.1
Database: DB2

Answer 1

If you want to create a table automatically, you can also use the following form:

 CREATE TABLE new_table_name AS (SELECT * FROM <querytableA> UNION SELECT * FROM <querytableB>) WITH DATA 

Note that you can create an on-demand view to dynamically build an on-demand result set. Then, in the form of a logical file, you can refer to any HLL:

 CREATE VIEW new_table_name AS SELECT * FROM <querytableA> UNION SELECT * FROM <querytableB> 

Answer 2

Here is the correct syntax:

 INSERT INTO <dest_table> Select * FROM <querytableA> UNION Select * FROM <querytableB> 

For this to work, <dest_table> must already exist and have columns compatible with the columns in <querytableA> and <querytableB> .

See " Insert rows with select-statement " for more information.