Create a Table from a query
Michele asked how to create a table from a query result in Informix, like CREATE TABLE AS SELECT. Serge Rielau noted SELECT INTO is how Informix works in principle. June C. Hunt gave SELECT ... INTO TEMP table [WITH NO LOG], or creating the permanent table first and using INSERT INTO ... SELECT. Michele thanked her. Solved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi, How can i create a table from the results of a query in Informix? In other databases I use CREATE TABLE xyz AS SELECT id FROM table1 or SELECT id INTO xyz FROM table1. Thanks. Mike
The latter (SELECT INTO <tablename> FROM is how Informix works in principle. Cheers Serge -- Serge Rielau DB2 SQL Compiler Development IBM Toronto Lab
Michele wrote: > How can i create a table from the results of a query in Informix? > In other databases I use CREATE TABLE xyz AS SELECT id FROM table1 or SELECT > id INTO xyz FROM table1. SELECT <field list> FROM <table> WHERE <condition> INTO TEMP <table name> <WITH NO LOG> If you want to populate a permanent table, create the new table first and then use something similar to: INSERT INTO <new permanent table> SELECT <field list> FROM <original table> See the Informix Guide to SQL manuals for more details. The following link will get you to the documentation: http://www-306.ibm.com/software/data/informix/pubs/library/list2.html -- June Hunt
> > How can i create a table from the results of a query in Informix? > > In other databases I use CREATE TABLE xyz AS SELECT id FROM table1 or > SELECT > > id INTO xyz FROM table1. > > SELECT <field list> > FROM <table> > WHERE <condition> > INTO TEMP <table name> <WITH NO LOG> > > If you want to populate a permanent table, create the new table first and > then use something similar to: > > INSERT INTO <new permanent table> > SELECT <field list> FROM <original table> > > See the Informix Guide to SQL manuals for more details. The following link > will get you to the documentation: > > http://www-306.ibm.com/software/data/informix/pubs/library/list2.html Thanks.