create existing table

Description

Creates a new proxy table representing an existing object in a remote server.

Syntax

 create existing table [database.[owner].]table_name (column_name datatype
    [default {constant_expression | user | null}]
     {[{identity | null | not null}]
     | [[constraint constraint_name]
         {{unique | primary key}
          [clustered | nonclustered]
          [with {fillfactor |max_rows_per_page}= x]
         [on segment_name]
         | references [[database.]owner.]ref_table
            [(ref_column)]
         | check (search_condition)}]}...
 
 | [constraint constraint_name]
     {{unique | primary key} 
         [clustered | nonclustered]
         (column_name [{, column_name}...])
          [with {fillfactor |max_rows_per_page}= x]
          [on segment_name]
     | foreign key (column_name [{, column_name}...])
         references [[database.]owner.]ref_table
            [(ref_column [{, ref_column}...])]
      | check (search_condition)}
 
 [{, {next_column | next_constraint}}...])
 
 [with max_rows_per_page = x] [on segment_name]
[external {table | procedure}]
[at “pathname”]

Usage

Adaptive Server processes the create existing table command as if the table being created is a new local table.
After creating the local table, Adaptive Server passes the create existing table command to Component Integration Services, with the external location for the existing remote object.

Component Integration Services verifies that the table exists by issuing the sp_tables RPC to the remote server that owns the existing object.
Component Integration Services verifies the column list by sending the sp_columns RPC to the remote server. Column names, datatypes, lengths, identity property, and null properties are checked for the following:
- Datatypes in the create existing table command must match or be convertible to the datatypes of the column on the remote location. For example, a local column datatype might be defined as money, while the remote column datatype might be numeric. This is a legal conversion, therefore, no errors are reported.
- 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 command is not aborted.
- Each column’s length is checked. If the length of char, varchar, binary, varbinary, decimal and numeric columns do not match, a warning message is issued, but the command is not aborted.
The column names used in the syntax must match with those found at the remote location.
The proxy table need not contain the exact number of columns as found in the remote table. However, all columns referenced in by the proxy table must be found in the remote table. If the count of columns in the proxy table is less than the actual number of columns in the remote server, then a warning is issued, but the command is not aborted.
The remote column name is stored in syscolumns.remote_name and is used during query processing when a statement is forwarded to the remote server. This name is not affected by sp_rename, so after the proxy table is created, if any column name is changed, it won’t affect processing of subsequent SQL commands.
Column datatypes do not need to be identical, but they must be convertible in both directions, or a column datatype mismatch error is raised, and the command is aborted.
The column length defined for columns of type char, varchar, binary, and varbinary must match the length of the corresponding columns in the remote table.
Scale and precision of columns of type numeric or decimal must match the scale and precision of the corresponding columns in the remote table.
If the null property is not identical to the remote column’s null property, a warning message is issued, but the command is not aborted.

Server Class ASEnterprise

Table 3-4 describes the allowable datatypes that can be used when mapping remote Adaptive Server columns to local proxy table columns:

**Table 3-4: Adaptive Server datatype conversions for create existing table**
Remote Adaptive Server Datatype	Allowable Adaptive Server Datatypes
binary(n)	image, binary(n), and varbinary(n); if not image, the length must match
bit	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
char(n)	text, nchar( n), nvarchar( n), char( n), varchar(n), unichar, univarchar; if not text, the length must match
datetime	datetime and smalldatetime
decimal(p, s)	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
float	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
image	image
int	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
money	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
nchar(n)	text, nchar( n), nvarchar( n), char( n), varchar( n); if not text, the length must match
numeric(p, s)	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
nvarchar(n)	text, nchar( n), nvarchar( n), char( n), varchar(n), unichar, univarchar; if not text, the length must match
real	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
smalldatetime	datetime and smalldatetime
smallint	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
smallmoney	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
text	text
timestamp	timestamp
tinyint	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
unichar	char, varchar, unichar, univarchar, text, datetime, and smalldatetime
univarchar	char, varchar, unichar, univarchar, text, datetime, and smalldatetime
varbinary(n)	image, binary(n), and varbinary(n); if not image, the length must match
varchar(n)	text, nchar( n), nvarchar( n), char( n), varchar(n) unichar, univarchar; if not text, the length must match

Server Class ASAnywhere

Table 3-5 describes the allowable datatypes that can be used when mapping remote Adaptive Server columns to local proxy table columns:

**Table 3-5: Adaptive Server Anywhere datatype conversions for create existing table**
Remote Adaptive Server Datatype	Allowable Adaptive Server Datatypes
binary(n)	image, binary(n), and varbinary( n); if not image, the length must match
bit	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
char(n)	text, nchar(n), nvarchar(n), char(n), varchar(n), unichar, univarchar; if not text, the length must match
datetime	datetime and smalldatetime
decimal(p, s)	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
float	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
image	image
int	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
money	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
nchar(n)	text, nchar(n), nvarchar(n), char(n), varchar(n), unichar, univarchar; if not text, the length must match
numeric(p, s)	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
nvarchar(n)	text, nchar(n), nvarchar(n), char(n), varchar(n), unichar, univarchar; if not text, the length must match
real	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
smalldatetime	datetime and smalldatetime
smallint	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
smallmoney	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
text	text
timestamp	timestamp
tinyint	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
varbinary(n)	image, binary(n), and varbinary(n), unichar, univarchar; if not image, the length must match
varchar(n)	text, nchar(n), nvarchar(n), char(n), varchar(n), unichar, univarchar; if not text, the length must match

Server Class ASIQ

text and image datatypes are not supported by ASIQ.
Other than text and image datatypes, behavior is the same as for server class ASAnywhere.

Server Class sql_server

Allowable datatype conversions are the same as for server class ASEnterprise.

Server Class direct_connect

The RPC sp_columns queries the datatypes of the columns in the existing table.

Local column datatypes do not need to be identical to remote column datatypes, but they must be convertible as shown in Table 3-6. If not, a column type error is raised, and the command is aborted.

**Table 3-6: DirectConnect datatype conversions for create existing table**
DirectConnect Datatype	Allowable Adaptive Server Datatypes
binary(n)	image, binary(n), varbinary(n); if the length does not match, the command is aborted
binary(16)	timestamp
bit	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
char(n)	text, nchar(n), nvarchar(n), char(n) and varchar(n), unichar, univarchar; if the length does not match, the command is aborted
datetime	datetime, smalldatetime
decimal(p, s)	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
float	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
image	image
int	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
money	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
nchar(n)	text, nchar(n), nvarchar(n), char(n) and varchar(n), unichar, univarchar; if the length does not match, the command is aborted
numeric(p, s)	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
nvarchar(n)	text, nchar(n), nvarchar(n), char(n) and varchar(n), unichar, univarchar; if the length does not match, the command is aborted
real	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
smalldatetime	datetime, smalldatetime
smallint	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
smallmoney	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
text	text
timestamp	timestamp, binary(8), varbinary(8)
unichar	text, nchar(n), nvarchar(n), char(n), varchar(n), unichar, univarchar; if not text, the length must match
univarchar	text, nchar(n), nvarchar(n), char(n), varchar(n), unichar, univarchar; if not text, the length must match

Datatype information is passed in the CS_DATAFMT structure associated with the parameter. The following fields of the structure contain datatype information:
- datatype – the CS_Library datatype representing the Adaptive Server datatype. For example, CS_INT_TYPE.
- usertype – the native DBMS datatype. sp_columns passes this datatype back to Component Integration Services during a create existing table command as part of its result set (see sp_columns in the Adaptive Server Reference Manual). Adaptive Server returns this datatype in the usertype field of parameters to assist the DirectConnect in datatype conversions.

Server Class db2

Column names are checked in a case-insensitive manner. If there is no match, a column name error is raised, and the command is aborted.

The Adaptive Server table can contain fewer columns than the remote table, but each column in the Adaptive Server table must have a matching column in the remote table.
text and image datatypes are not supported by server class db2.

When a create existing table command is processed, the datatype for each column specifies the type of conversion to perform between the DB2 and Adaptive Server datatypes during query processing. Table 3-7 describes the allowable Adaptive Server datatypes that can be used for existing DB2 datatypes:

**Table 3-7: DB2 datatype conversions for create existing table**
DB2 Datatype	Allowable Adaptive Server Datatypes
int	int
smallint	int, smallint, and tinyint; if length does not match, a warning message is issued
tinyint	int, smallint, and tinyint; if length does not match, a warning message is issued
float	real, float, and money
double precision	real, float, and money
real	real, float, and money
decimal(scale > 0)	float, money, decimal, and numeric; for decimal and numeric, scale and precision must match
decimal (scale = 0)	float, money, decimal, and numeric; for decimal and numeric, scale and precision must match
numeric (scale > 0)	float, money, decimal, and numeric; for decimal and numeric, scale and precision must match
numeric (scale = 0)	float, money, decimal, and numeric; for decimal and numeric, scale and precision must match
char	char, varchar, bit, binary, varbinary, text and image, unichar, univarchar; if not text or image, length must match
char(n) for bit data	binary(n), varbinary(n), unichar, univarchar, and image; if not image, length must match
varchar	char, varchar, bit, binary, varbinary,unichar, univarchar, text and image; if not text or image, length must match
varchar(n) for bit data	binary(n), varbinary(n),unichar, univarchar, and image; if not image, length must match
long varchar (length of maximum column size depends on the logical page size of the Adaptive Server)	char, varchar, bit, binary, varbinary, unichar, univarchar, text and image; if not text or image, length must match
date	char(10), varchar(10), and datetime (time set to 12:00AM)
time	char(8), varchar(8), and datetime (date set to 1/1/1900)
timestamp	char(26), varchar(26), datetime, and smalldatetime
graphic	Not supported
vargraphic	Not supported
long vargraphic	Not supported

If the data contained in a long varchar column exceeds the length allowed by the logical page size of the Adapted Server, it is truncated, or, if the gateway is so configured, an error is returned.
DB2 table names are limited to 18 characters.
DB2 authorization IDs (owner names) are limited to 8 characters.
The maximum string length for columns returned by DB2 is 254 characters for char and varchar datatypes. For long varchar, the length is 32,704 bytes.
DB2 can return date values that are not within the range of the Adaptive Server datetime datatype. DB2’s range is 0001-01-01 to 9999-12-31. The Adaptive Server’s range is 1753-01-01 to 9999-12-31. When a date earlier than 1753-01-01 is retrieved from DB2, it is converted to 1753-01-01.
Check DB2 documentation for the maximum number of columns per DB2 table. This varies with the DB2 version.