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. TheINSERT
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
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>