create a table same as other table
The original poster wanted to create a static table B with the same structure as table A on the fly, without SELECT INTO TEMP. Jonathan Leffler said there was nothing very nice: dissect the system catalog and build the DDL, and he pointed to Art Kagel's myschema. Steven Hauser suggested dbschema or a schema utility. Flavio Gattari noted Oracle's CREATE TABLE AS SELECT has no Informix equivalent.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
During a process I want to create a table B same as other table A. I don't want to explicitly create the table B because I do not want to change it everytime I make a change in the structure of table A. Table B should be created on the fly based on the current structure of table A. Table B is not a temp table. It is a static table. Hence I can not use select into temp table B syntax. What other option do I have? TIA.
triggerfish2001@hotmail.com wrote: > During a process I want to create a table B same as other > table A. I don't want to explicitly create the table B because > I do not want to change it everytime I make a change in the > structure of table A. Table B should be created on the fly based > on the current structure of table A. > > Table B is not a temp table. It is a static table. Hence I can > not use select into temp table B syntax. > > What other option do I have? Nothing very nice, I think. You probably have to dissect the system catalog and build yourself an alternative specification for the table and then build it. That is not trivial, in general. How exactly does the current structure have to be replicated? The indexes? The check constraints? What about index names? Referential constraints to other tables? The dbspaces it is in? Locking mode? Etc. Try Art Kagel's myschema for some ideas. Does another database vendor make this particularly easy? If so, how easy? -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"
Several choices:
Get the DDL for the table from the system catalog or dbschema or
one of the free schema utilities and create the table. Make
a system call to do it from whatever language you are using.
--
---------------------------------------------------------
Steven Hauser
email: hause011@tc.umn.edu URL: http://www.sofbot.com/
---------------------------------------------------------
>
>Nothing very nice, I think. You probably have to dissect the system
>catalog and build yourself an alternative specification for the table
>and then build it. That is not trivial, in general. How exactly does
>the current structure have to be replicated? The indexes? The check
>constraints? What about index names? Referential constraints to other
>tables? The dbspaces it is in? Locking mode? Etc. Try Art Kagel's
>myschema for some ideas.
>
>Does another database vendor make this particularly easy? If so, how
>easy?
>
ORACLE has the nice
CREATE TABLE B AS SELECT * FROM TABLE A
which, simply, makes a COPY of table A (within triggers, constraints, etc)
It copies data, too. But it has also the nice TRUNCATE TABLE, to delete
every data in table B immediatly after the creation.
Neither CREATE AS SELECT nor TRUNCATE TABLE are available in Informix,
AFAIK, (truncate is available only in XPS). It has, obviously, some other
nice things that oracle hasn't.
>--
>Yours,
>Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h>
>Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
> "I don't suffer from insanity; I enjoy every minute of it!"