Recently a friend of mine who meets a lot of Informix customers told me he frequently gets a question about copying a table with all the data in it and suggested that could be a good idea for a small utility. Having a SELECT …INTO permanent_table syntax available in Informix 12.10, there wouldn’t be much sense in creating such utility, but there’s another request – would be very nice if the constraints (primary and foreign keys, etc.) were copied as well.
Being a small piece of code, it would be most reasonable if it could be executed in a database itself, with no need for additional software to run it, and every DBA should be able to run some SQL statements. So I wrote a couple of stored procedures (well, functions to be exact) and these are actually all you need to run it. This little project is also open-sourced, released under GNU GPL v3 license, hosted on GitHub as ifmx-table-copy. There are two functions you need to create in your database (links lead to SQL files): getIdxColumnsForTableCopy and tableCopy.
When tableCopy is executed, it will create a copy of the table, with a new name, populate it with the data from the source table, and create the primary key, foreign keys, check, not null, and unique constraints and also indexes that exists on the source table, on the corresponding columns of the target table. The names of constraints are created from or concatenated with the target table name (see the details on the project page). The default values that exists on source table columns are also stated in the target table, provided they refer to character types. It is possible to state a different dbspace in which the table will be created. If no dbspace is given (i.e. the corresponding argument is null), then the table is created in the default database dbspace. There is also a parameter that prevents the execution of all the statements that would be executed during the target table creation – instead the statements are returned from the function and can be examined before the “real” execution.
Execute the tableCopy function with the following arguments to see what will actually be executed on the server:
execute function tablecopy('source_table_name', 'target_table_name', null, 'f');
Execute the following to perform actual structure creation and data copy:
execute function tablecopy('source_table_name', 'target_table_name', 'db_space', 't');
This utility uses the SQL syntax available in the Informix servers 11.70 and newer. There are some minor modifications that could be done to achieve backward compatibility. For more info on that, please see the project’s readme file.