CREATE EXISTING TABLE Statement

Creates a new proxy table that represents an existing table on a remote server.

Syntax

CREATE EXISTING TABLE [owner.]table_name 
[ ( column-definition, … ) ] 
AT 'location-string'

Parameters

column-definition: – column-name data-type [ NOT NULL ]
location-string: – remote-server-name.[db-name].[owner].object-name | remote-server-name;[db-name];[owner];object-name

Examples

Example 1 – Create a proxy table named nation for the nation table at the remote server server_a:

CREATE EXISTING TABLE nation
( n_nationkey int,
  n_name char(25),
  n_regionkey int,
  n_comment char(152))
AT 'server_a.db1.joe.nation'

Example 2 – Create a proxy table named blurbs for the blurbs table at the remote server server_a. Sybase IQ derives the column list from the metadata it obtains from the remote table:
```
CREATE EXISTING TABLE blurbs
AT 'server_a.db1.joe.blurbs'
```
Example 3 – Create a proxy table named rda_employee for the Employees table at the Sybase IQ remote server remote_iqdemo_srv:
```
CREATE EXISTING TABLE rda_employee
AT 'remote_iqdemo_srv..dba.Employees'
```

Usage

CREATE EXISTING TABLE is a variant of the CREATE TABLE statement. The EXISTING keyword is used with CREATE TABLE to specify that a table already exists remotely, and that its metadata is to be imported into Sybase IQ. This establishes the remote table as a visible entity to its users. Sybase IQ verifies that the table exists at the external location before it creates the table.

Tables used as proxy tables cannot have names longer than 30 characters.

If the object does not exist (either as a host data file or remote server object), the statement is rejected with an error message.

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 might exist on this table.

Referential constraints are passed to the remote location when appropriate.

If you do not specify column definitions, Sybase IQ derives the column list from the metadata it obtains from the remote table. If you do specify column definitions, Sybase IQ verifies them. When Sybase IQ checks column names, data types, lengths, and null properties:

Column names must match identically (although case is ignored).
Data types in CREATE EXISTING TABLE must match or be convertible to the data types of the column on the remote location. For example, a local column data type is defined as NUMERIC, whereas the remote column data type is MONEY. You may encounter some errors, if you select from a table in which the data types do not match or other inconsistencies exist.
Each column’s NULL property is checked. If the local column’s NULL property is not identical to the remote column’s NULL property, a warning message is issued, but the statement is not aborted.
Each column’s length is checked. If the lengths of CHAR, VARCHAR, BINARY, DECIMAL, and NUMERIC columns do not match, a warning message is issued, but the command is not aborted. You might choose to include only a subset of the actual remote column list in your CREATE EXISTING statement.
AT specifies the location of the remote object. The AT clause supports the semicolon (;) as a delimiter. If a semicolon is present anywhere in the location string, the semicolon is the field delimiter. If no semicolon is present, a period is the field delimiter. This allows you to use file names and extensions in the database and owner fields. Semicolon field delimiters are used primarily with server classes that are not currently supported; however, you can also use them where a period would also work as a field delimiter. For example, this statement maps the table proxy_a1 to the SQL Anywhere database mydb on the remote server myasa:
```
CREATE EXISTING TABLE 
proxy_a1
AT 'myasa;mydb;;a1'
```

In a simplex environment, you cannot create a proxy table that refers to a remote table on the same node. In a multiplex environment, you cannot create a proxy table that refers to the remote table defined within the multiplex.

For example, in a simplex environment, if you try to create proxy table proxy_e, which refers to base table Employees defined on the same node, the CREATE EXISTING TABLE statement is rejected with an error message. In a multiplex environment, the CREATE EXISTING TABLE statement is rejected if you create proxy table proxy_e from any node (coordinator or secondary) that refers to remote table Employees defined within a multiplex.

See also System Administration Guide: Volume 2 > Accessing Remote Data and System Administration Guide: Volume 2 > Server Classes for Remote Data Access.

Standards

SQL—ISO/ANSI SQL compliant.
Sybase—Supported by Open Client/Open Server.

Permissions

Must have RESOURCE authority. To create a table for another user, you must have DBA authority.