The create existing table command allows the definition of existing tables (proxy tables). The syntax for this option is similar to the create table command and reads as follows:
create existing table table_name (column_list)
[ on segment_name ]
[[external {table | procedure | file}] at pathname]
The action taken by the server when it receives this command is quite different from the action it takes when it receives the create table command, however. In this case, a new table is not created at the remote location; instead, the table mapping is checked, and the existence of the underlying object is verified. If the object does not exist (either host data file or remote server object), the command is rejected with an error message.
If the object does exist, its attributes are obtained and used to update system tables sysobjects, syscolumns, and sysindexes.
The nature of the existing object is determined.
For remote server objects (other than RPCs), column attributes found for the table or view are compared with those defined in the column_list. Column names must match identically (although case is ignored), column types and lengths must match, or at least be convertible, and the NULL attributes of the columns must match.
Index information from the host data file or remote server table is extracted and used to create rows for the system table sysindexes. This defines indexes and keys in server terms and enables the query optimizer to consider any indexes that may exist on this table.
The on segment_name clause is processed locally and is not passed to a remote server.
After successfully defining an existing table, issue an update statistics command for the table.This allows the query optimizer to make intelligent choices regarding index selection and join order.
