CREATE EXISTING TABLE statement

CREATE EXISTING TABLE [owner.]table-name
[ column-definition, ... ]
AT location-string | variable-name

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

AT clause The AT clause 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 string, the semicolon is the field delimiter. If no semicolon is present, a period is the field delimiter. This behavior allows file names and extensions to be used in the database and owner fields. For example, the following statement maps the table a1 to the Microsoft Access file mydbfile.mdb:

CREATE EXISTING TABLE a1 AT 'access;d:\mydbfile.mdb;;a1';

When the proxy table is used, variable-name is replaced with the contents of the SQL variable variable-name that must be of the type CHAR, VARCHAR, or LONG VARCHAR. For more information about using variables in the AT clause, see Example 2 in Creating directory access servers (Sybase Central).

The CREATE EXISTING TABLE statement creates a new, local, proxy table that maps to a table at an external location. The CREATE EXISTING TABLE statement 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 SQL Anywhere. This establishes the remote table as a visible entity to SQL Anywhere users. SQL Anywhere verifies that the table exists at the external location before it creates the table.

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 ISYSIDX system table. This information defines indexes and keys in server terms and enables the query optimizer to consider any indexes that may exist on this table.

Referential constraints are passed to the remote location when appropriate.

If column-definitions are not specified, SQL Anywhere derives the column list from the metadata it obtains from the remote table. If column-definitions are specified, SQL Anywhere verifies the column-definitions. Column names, data types, lengths, the identity property, and null properties are checked for the following:

Column names must match identically (although case is ignored).
Data types in the CREATE EXISTING TABLE statement 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 money, while the remote column data type is numeric.
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 length of CHAR, VARCHAR, BINARY, VARBINARY, DECIMAL and/or NUMERIC columns do not match, a warning message is issued, but the command is not aborted.

You may choose to include only a subset of the actual remote column list in your CREATE EXISTING statement.

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

Not supported on Windows Mobile.

Automatic commit.

SQL/2008 Vendor extension.
Transact-SQL Supported by Adaptive Server Enterprise. The format of location-string is implementation-defined.

Create a proxy table named blurbs for the blurbs table at the remote server server_a.

CREATE EXISTING TABLE blurbs
( author_id ID not null,
copy text not null)
AT 'server_a.db1.joe.blurbs';

Create a proxy table named blurbs for the blurbs table at the remote server server_a. SQL Anywhere derives the column list from the metadata it obtains from the remote table.

CREATE EXISTING TABLE blurbs
AT 'server_a.db1.joe.blurbs';

Create a proxy table named rda_employees for the Employees table at the remote SQL Anywhere Server, demo12.

CREATE EXISTING TABLE rda_employees
AT 'demo12...Employees';

CREATE EXISTING TABLE statement

Syntax

Parameters

Remarks

Permissions

Side effects

See also

Standards and compatibility

Examples