Creating proxy tables (SQL)

Administrators can create proxy tables in Interactive SQL using either the CREATE TABLE or CREATE EXISTING TABLE statement.

Prerequisites

DBA authority.

Context and remarks

The CREATE TABLE statement creates a new table on the remote server, and defines the proxy table for that table when you use the AT clause. Columns are defined using SQL Anywhere data types. SQL Anywhere automatically converts the data into the remote server's native types.

If you use the CREATE TABLE statement to create both a local and remote table, and then subsequently use the DROP TABLE statement to drop the proxy table, the remote table is also dropped. You can, however, use the DROP TABLE statement to drop a proxy table created using the CREATE EXISTING TABLE statement. In this case, the remote table is not dropped.

The CREATE EXISTING TABLE statement creates a proxy table that maps to an existing table on the remote server. SQL Anywhere derives the column attributes and index information from the object at the remote location.

Create a proxy table with the CREATE EXISTING TABLE statement

Connect to the host database as a user with DBA authority.
Execute a CREATE EXISTING TABLE statement.

Results

The proxy table is created.

Next

None.

Example

To create a proxy table called p_Employees on the current server that maps to a remote table named Employees on the server named RemoteSA, use the following syntax:

CREATE EXISTING TABLE p_Employees
AT 'RemoteSA..GROUPO.Employees';

Example

The following statement maps the proxy table a1 to the Microsoft Access file mydbfile.mdb. In this example, the AT clause uses the semicolon (;) as a delimiter. The server defined for Microsoft Access is named access.

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

Example

The following statement creates a table named Employees on the remote server RemoteSA, and creates a proxy table named Members that maps to the remote table:

CREATE TABLE Members
( membership_id INTEGER NOT NULL,
member_name CHAR( 30 ) NOT NULL,
office_held CHAR( 20 ) NULL )
AT 'RemoteSA..GROUPO.Employees';

Creating proxy tables (SQL)

Create a proxy table with the CREATE EXISTING TABLE statement

Example 1

See also