Insert Into Temp Values (select.... Order By Id)

I'm using an Informix (Version 7.32) DB. On one operation I create a temp table with the ID of a regular table and a serial column (so I would have all the IDs from the regular ta

Solution 1:

You can do this by breaking up the SQL into two temp tables:

CREATE TEMP TABLE tempTable1 (
id serial,
folio int);

SELECT folio FROM regularTable ORDERBY folio
INTO TEMP tempTable2;

INSERTINTO tempTable1(id,folio) SELECT0,folio FROM tempTable2;

Solution 2:

In Informix when using a SELECT as a sub-clause in an INSERT statement, you are limited to a subset of the SELECT syntax.

The following SELECT clauses are not supported in this case:

• INTO TEMP
• ORDER BY
• UNION.

Additionally, the FROM clause of the SELECT can not reference the same table as referenced by the INSERT (not that this matters in your case).

Solution 3:

It's been years since I worked on Informix, but perhaps something like this will work:

INSERTINTO tempTable(id,folio)
SELECT0, folio 
FROM (
    SELECT folio FROM regularTable ORDERBY folio
);

Solution 4:

You might try it iterating a cursor over the SELECT ... ORDER BY and doing the INSERTs within the loop.

Solution 5:

It makes no sense to order the rows as you insert into a table. Relational databases do not allow you to specify the order of rows in a table.

Even if you could, SQL does not guarantee a query will return rows in any order, such as the order you inserted them. You must specify an ORDER BY clause to guarantee an order for a query result.

So it would do you no good to change the order in which you insert the rows.