INSERT INTO ... UNION - unsupported!?

[s***@gmail.com]

Years and years after the fact, but maybe someone finds it useful.
Informix's INSERT INTO seems to only allow one SELECT statement.
The solution is to select from the union.

INSERT INTO mytbl
SELECT * FROM
(
SELECT "B", "B" FROM mytblB
UNION
SELECT "C", "C" FROM mytblB
);

[s***@gmail.com]

Hi Folks,
We have some SQL that inserts a UNION into a table. It works with SQL
Server 7.0 and Cloudscape, but not on Informix IDS.2000 9.21.UC3-2 on

As indicated in the diagram for INSERT on page 2-535, not all clauses and
options of the SELECT statement are available for you to use in an INSERT
statement.
n FIRST
n INTO TEMP
n ORDER BY
n UNION *

I can't believe a product this mature would have such a significant
limitation. What a major pain in the butt! Would someone please tell me
1) that I'm not having a nightmare - this limitation is reality, and if
so, 2) how people work around it? (I've considered inserting into a
temporary table, and using views, though I think the latter won't work
with the INSERT.)
We are new Informix users and we really *want* to like the product, but
jeez!
matt

Hi Matt
Years and years after the fact, but maybe someone finds it useful.
Informix's INSERT INTO seems to only allow one SELECT statement.
The solution is to select from the union.

INSERT INTO mytbl
SELECT * FROM
(
SELECT "B", "B" FROM mytblB
UNION
SELECT "C", "C" FROM mytblB
);