Copy tables in informix

Question

How can I efficiently copy a table within an informix database? I would like to do something like

create table new_table as (select * from old_table)

but that doesn't work.

Answer 1

If you only need a temporary table, then:

SELECT * FROM old_table INTO TEMP new_table;

If you need a permanent table, then there isn't (yet) a simple way to do it. You have to determine the schema of the old table, use it to create the new table, then use:

INSERT INTO new_table SELECT * FROM old_table;

The fiddly bit is determining the schema of the old table. dbschema -d database -t old_table more or less provides the information you need.