CREATE EXISTING TABLE statement

Creates a new proxy table, which represents an existing object on a remote server.

Syntax

CREATE { EXISTING | VIRTUAL } TABLE [owner.]table-name
[ column-definition, ... ]
AT location-string [ ESCAPE CHARACTER character ]

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

Parameters

CREATE { EXISTING | VIRTUAL } TABLE clause
CREATE EXISTING TABLE and CREATE VIRTUAL TABLE are semantically equivalent. CREATE VIRTUAL TABLE is provided for compatibility with SAP HANA.
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, then the semicolon is the field delimiter. If no semicolon is present, then a period is the field delimiter. This behavior allows file names and extensions to be used in the database and owner fields. An ESCAPE CHARACTER clause allows applications to escape these delimiters within a location string.
When you create a proxy table by using either the CREATE TABLE or the CREATE EXISTING statement, the AT clause includes a location string that is comprised of the following parts:
- The name of the remote server
- The remote catalog
- The remote owner or schema
- The remote table name
Use a period or semicolon to delimit the location strings. The location string can also contain variable names that are expanded when the database server evaluates the location string. Variable names within the location string are encapsulated within braces. It is very rare to have a period, semicolon, and a brace, or just a brace, be part of a remote server name, catalog name, owner name, schema name, or table name. However, there may be some situations where one or all of these delimiter characters must be interpreted literally within a location string.
Note
The ESCAPE clause is only necessary if there is a need to escape delimiters within the location clause. In general, the ESCAPE clause can be omitted when creating proxy tables. The escape character can be any single byte character.

The string in the AT clause can contain local or global variable names enclosed in braces (for example, {variable-name}). The SQL variable name must be of type CHAR, VARCHAR, or LONG VARCHAR. For example, an AT clause that contains 'access;{@myfile};;a1' indicates that @myfile is a SQL variable and that the current contents of the @myfile variable should be substituted when the proxy table is created.

Remarks

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 to import its metadata. This syntax establishes the remote table as a visible entity to users. The software 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, then the database server derives the column list from the metadata it obtains from the remote table. If column-definitions are specified, then the database server verifies the column-definitions. Column names, data types, lengths, the identity property, and null properties are checked for the following conditions:

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, then 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, then 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.

Privileges

You must have the CREATE PROXY TABLE system privilege to create proxy tables owned by you. You must have the CREATE ANY TABLE or CREATE ANY OBJECT system privilege to create proxy tables owned by others.

Side effects

Automatic commit.

Standards

ANSI/ISO SQL Standard
Not in the standard.
Transact-SQL
Supported by Adaptive Server Enterprise. The format of location-string is implementation-defined.

Example

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. The database server 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 server rda.

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

Create a proxy table named rda_employees for a table that is specified by the SQL variable table_name at the remote server rda.

CREATE EXISTING TABLE rda_employees
AT 'rda...{table_name}';

To utilize the ESCAPE CHARACTER clause, consider the following example:

Create two SQL Anywhere databases named db1 and db2.
Start both databases on the same server:
```
dbsrv17 -n escape_test test1.db test2.db
```

Connect to test2 and create the following table:

CREATE TABLE "table.with;fun{characters}"(c int);
INSERT INTO "table.with;fun{characters}" VALUES(100);
COMMIT;

Disconnect and connect to test1.
Create a remote server to test2 as follows:
```
CREATE SERVER test2_server CLASS 'saodbc' USING 'driver=SQL Anywhere Native;eng=escape_test;dbn=test2';
CREATE EXTERNLOGIN localuser TO test2_server REMOTE LOGIN remoteuser IDENTIFIED BY remotepwd;
```
Note localuser is the userid used to log in to test1 while remoteuser and remotepwd are the remote userid and password needed to log in to test2.
The ESCAPE CHARACTER clause can be used to create a proxy table for the remote "table.with;fun{characters}" as follows:
```
CREATE EXISTING TABLE remtab AT 'test2_server;;;table.with!;fun!{characters!}'ESCAPE CHARACTER'!';
```
OR
```
CREATE EXISTING TABLE remtab AT 'test2_server...table!.with!;fun!{characters!}'ESCAPE CHARACTER'!';
```
Optionally, you can execute a query on the proxy table to ensure you get the expected result set back:
```
SELECT c FROM remtab;
```