How to move a DB2 SQL result table into a physical file?

Question

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

I was hoping to use (per W3Schools):

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

Unfortunately per the IBM Manual (DB2 for i SQL reference)

The SELECT INTO statement produces a result table consisting of at most one row, and assigns the values in that row to variables.

So then I thought maybe I could use:

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

Unfortunately it does not seem to work for me.

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

I am able to move a result table into a physical file, but to do this I have to declare a cursor and then with a do … while write to the physical file, one record at a time. I consider this ugly and unelegant and was hoping to be able to use a SELECT INTO or a INSERT INTO statement.

Please help me “dump” the result table into a physical file with one elegant statement.

OS: IBM i v6.1
Database: DB2

Solution 1

If you want to create the 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 a view over the query to dynamically build the result set on demand. The view can then be referenced from any HLL as a logical file:

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

Solution 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 those in <querytableA> and <querytableB>.

See "Inserting rows using a select-statement" for more info.

Comments

[Christoff Erasmus]

This is exactly what I wanted to do. But I some how got the columns wrong in the table I created. Thanks to a combination of both your and @JamesA's answers I was able to solve the problem.

[Thorsten Niehues]

WITH DATA does not work on our DB2 10.5 only WITH NO DATA !!?? Is it a setting or version issue?