create existing table

Server class ASEnterprise

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

**Table 3-11: 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, smalldatetime, char, and varchar
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, smalldatetime, char, and varchar
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, unitext
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
unitext	unitext
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
date
time
bigint	Implicit: binary, varbinary, bit, tinyint, smallint, int, decimal, numeric, float, real, money, smallmoney Explicit: char, varchar, unichar, univarchar
unsigned tinyint	Implicit: binary, varbinary, bit, tinyint, smallint, unsigned smallint, int, unsigned int, bigint, unsigned bigint, decimal, numeric, float, real money, smallmoney Explicit: char, varchar, unichar, univarchar Unsupported: text, image, date, time, datetime, smalldatetime
unsigned smallint	Implicit: binary, varbinary, bit, tinyint, smallint, unsigned smallint, int, unsigned int, bigint, unsigned bigint, decimal, numeric, float, real money, smallmoney Explicit: char, varchar, unichar, univarchar Unsupported: text, image, date, time, datetime, smalldatetime
unsigned int	Implicit: binary, varbinary, bit, tinyint, smallint, unsigned smallint, int, unsigned int, bigint, unsigned bigint, decimal, numeric, float, real money, smallmoney Explicit: char, varchar, unichar, univarchar Unsupported: text, image, date, time, datetime, smalldatetime
unsigned bigint	Implicit: binary, varbinary, bit, tinyint, smallint, unsigned smallint, int, unsigned int, bigint, unsigned bigint, decimal, numeric, float, real money, smallmoney Explicit: char, varchar, unichar, univarchar Unsupported: text, image, date, time, datetime, smalldatetime

Component Integration Services only supports unitext with Adaptive Server version 15.0 and higher.

Server class ASAnywhere

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

**Table 3-12: Adaptive Server Anywhere datatype conversions for create existing table**
Remote Adaptive Server Anywhere datatype	Allowable Adaptive Server Anywhere 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
numeric(p, s)	bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint
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, unitext, 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
date
time
bigint	Implicit: binary, varbinary, bit, tinyint, smallint, unsigned smallint, int, unsigned int, bigint, unsigned bigint, decimal, numeric, float, real money, smallmoney Explicit: char, varchar, unichar, univarchar Unsupported: text, image, date, time, datetime, smalldatetime
unsigned tinyint	Implicit: binary, varbinary, bit, tinyint, smallint, unsigned smallint, int, unsigned int, bigint, unsigned bigint, decimal, numeric, float, real money, smallmoney Explicit: char, varchar, unichar, univarchar Unsupported: text, image, date, time, datetime, smalldatetime
unsigned smallint	Implicit: binary, varbinary, bit, tinyint, smallint, unsigned smallint, int, unsigned int, bigint, unsigned bigint, decimal, numeric, float, real money, smallmoney Explicit: char, varchar, unichar, univarchar Unsupported: text, image, date, time, datetime, smalldatetime
unsigned int	Implicit: binary, varbinary, bit, tinyint, smallint, unsigned smallint, int, unsigned int, bigint, unsigned bigint, decimal, numeric, float, real money, smallmoney Explicit: char, varchar, unichar, univarchar Unsupported: text, image, date, time, datetime, smalldatetime
unsigned bigint	Implicit: binary, varbinary, bit, tinyint, smallint, unsigned smallint, int, unsigned int, bigint, unsigned bigint, decimal, numeric, float, real money, smallmoney Explicit: char, varchar, unichar, univarchar Unsupported: text, image, date, time, datetime, smalldatetime

Server class ASIQ

text and image datatypes are supported by ASIQ version 12.6 and requires a license.
Behavior is the same as for server class ASAnywhere.

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-13. If not, a column type error is raised and the command is aborted.

**Table 3-13: 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
date
time
bigint	UDB and DC/Microsoft support bigint.

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 Reference Manual). Adaptive Server returns this datatype in the usertype field of parameters to assist the DirectConnect in datatype conversions.

Usage

When a create existing table command is received, it is interpreted as a request to import metadata from the remote or external location of the object for updating system catalogs. Importing this metadata is performed by means of three RPCs sent to the remote server with which the object has been associated:

sp_tables – verifies that the remote object actually exists.
sp_columns – obtains column attributes of the remote object for comparison with those defined in create existing table.
sp_statistics – obtains index information to update the local system table, sysindexes.