Insert into temp values (select.... order by id)

Question

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 table numbered continuously). But I want to insert the info from the regular table ordered by ID something like:

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

INSERT INTO tempTable(id,folio)
SELECT 0,folio FROM regularTable ORDER BY folio;

But this creates a syntax error (because of the ORDER BY)

Is there any way I can order the info then insert it to the tempTable?

UPDATE: The reason I want to do this is because the regular table has about 10,000 items and in a jsp file, it has to show every record, but it would take to long, so the real reason I want to do this is to paginate the output. This version of Informix doesn't have Limit nor Skip. I can't renumber the serial because is in a relationship, and this is the only solution we could get a fixed number of results on one page (for example 500 results per page). In the Regular table has skipped id's (called folio) because they have been deleted. if i were to put

SELECT * FROM regularTable WHERE folio BETWEEN X AND Y

I would get maybe 300 in one page, then 500 in the next page

Answer 1

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

Answer 2

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

INSERT INTO tempTable(id,folio)
SELECT 0, folio 
FROM (
    SELECT folio FROM regularTable ORDER BY folio
);

Answer 3

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

Answer 4

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.

Answer 5

As stated by Bill, there's not a lot of point ordering the input, you really need to order the output. In the simplistic example you've provided, it just makes no sense, so I can only assume that the real problem you're trying to solve is more complex - deduplication perhaps?

The functionality you're after is CREATE SEQUENCE, but I'm pretty sure it's not available in such an old version of Informix.

If you really need to do what you're asking, you could look into UNLOADing the data in the required order, and then LOADing it again. That would ensure the SERIAL values get allocated sequentially.

Answer 6

Would something like this work?

SELECT
    folio
FROM
    (
        SELECT
            ROWNUM n,
            folio
        FROM
            regularTable
        ORDER BY 
            folio
    )
WHERE
    n BETWEEN 501 AND 1000

It may not be terribly efficient if the table grows larger or you're fetching later "pages", but 10K rows is pretty small.

I don't recall if Informix has a ROWNUM concept, I use Oracle.