COPY STRUCTURE, COPY STRUCTURE Extended, CREATE FROM

These commands let you duplicate the structure of an existing table, or create a similar but not identically structured table. They used to be extremely important commands, but newer commands do all the same things more easily.

Usage

COPY STRUCTURE TO cTable
        [ DATABASE DatabaseName [ NAME LongTableName ] ]
        [ FIELDS cFieldList ]
        [ [ WITH ] CDX | PRODUCTION ]

COPY STRUCTURE creates a new table with a structure identical to the table open in the current work area. If the optional field list is included, only the listed fields are included in the new table. The newly created table is not opened. Including the optional CDX or PRODUCTION clause, with or without the keyword WITH, also duplicates the structural compound index file for the table. The DATABASE and NAME clauses let you put the new table in an existing database and give it a long name; doing so fires the BeforeAddTable and AfterAddTable events in the target database if Database Events are turned on in VFP 7 or later.

When you specify a database to add the new table to, COPY STRUCTURE doesn't preserve the rules, triggers, and so on in the new table that were associated with the original table. Null, code page, and long field name information is preserved. In VFP 3, the information was lost because you couldn't put the new table in a database.

COPY STRUCTURE is probably the most useful command in this group because we do occasionally find that we need to create an exact clone of an existing table.

Example

USE Orders
* create a table to create archives
COPY STRUCTURE TO OldOrders

Usage

COPY STRUCTURE EXTENDED TO cStructureTable
        [ DATABASE DatabaseName [ NAME LongTableName ] ]
        [ FIELDS cFieldList ]

COPY STRUCTURE EXTENDED creates a new table containing information about the table open in the current work area. It contains 16 fields representing the 16 attributes needed to create a table in a DBC. The list of fields created is identical to the columns created by AFIELDS().

If the FIELDS clause is included, records are created only for the listed fields. The table-level information (the last seven fields) appears only in the first record of the new table.

You can indicate that the table is to be added to a database and even give it a long name in that database; as with COPY STRUCTURE, doing so fires the BeforeAddTable and AfterAddTable events in the target database (in VFP 7 or later if Database Events are turned on). We’re not quite sure why you’d want a table that holds the structure of another table in a database—maybe for some kind of data dictionary.

COPY STRUCTURE EXTENDED used to be the standard way to modify the structure of an existing table. You’d create this table, then modify it as needed (since it was a table after all), then you could use CREATE FROM to create a new table and copy the data from the old. Rename the new table and you were on your way. Beginning in FoxPro 2, though, better ways came along. Either a combination of AFIELDS() and CREATE TABLE or a simple SELECT-SQL was generally simpler. In Visual FoxPro, you don’t need to go to any of these extremes. To modify the structure of an existing table, you can simply ALTER TABLE.

In addition to ALTER TABLE being a better way to modify an existing table, COPY STRUCTURE EXTENDED simply doesn’t get enough information to let you completely re-create a table. (Neither does AFIELDS().) The caption, format, input mask, display library, and display class properties of fields are omitted, and no indexes are created. In VFP 3, table triggers were omitted as well.

Usage

CREATE cTable [ DATABASE DatabaseName NAME LongTableName ]
       FROM cStructureTable

CREATE FROM creates a new table, taking structure information from a table like the ones created by COPY STRUCTURE EXTENDED. The structure table can have the same 16 columns shown in the table above, but you can actually get away with only the first four or the first 11. Using any other set of the columns generates a “Structure is invalid” error in VFP.

Keep in mind that there is a limit of 255 fields per table. If you hand CREATE FROM a table with more than 255 records, there’s no guaranteeing what it’ll do. In some cases, VFP does something reasonably intelligent under the circumstances, but in others it gets downright weird.

Like the other versions of CREATE, CREATE FROM leaves the new table open in the current work area. You can indicate that the new table is to be added to a database and given a long table name; this fires the BeforeCreateTable and AfterCreateTable events in the target database if Database Events are turned on. In VFP 3, if a database is open when you issue CREATE FROM, the new table is added to the database.

CREATE FROM doesn't ignore deleted records in the structure table. This means if you COPY STRUCTURE EXTENDED, then modify the table by deleting some of the records (that is, remove fields from the listing), you need to PACK the structure table before issuing CREATE FROM. Otherwise, your new table will have the fields you tried to remove. This bug is nothing new—older versions do the same thing.

Given the various problems and peculiarities of these commands and the fact that there are better ways to do everything they do, we recommend you stay away from them.

See Also

AFields(), Alter Table, AfterAddTable, AfterCreateTable, BeforeAddTable, BeforeCreateTable, Copy To, Create Cursor, Create Table, Select-SQL