create a table same as other table

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!"