create existing table


(Component Integration Services only) Creates a proxy table, then retrieves and stores metadata from a remote table and places the data into the proxy table. Allows you to map the proxy table to a table, view, or procedure at a remote location.

The preferred method of creating proxy tables is the create proxy_table command, which eliminates the need to define the column definitions.


create existing table table_name (column_list) 
	[on segment_name]
	[[external {table | procedure | file | connection_type}] at pathname 
	[column delimiter "string"]]



specifies the name of the table for which you want to create a proxy table.


specifies the name of the column list that stores information about the remote table.

on segment_name

specifies the segment that contains the remote table.


specifies that the object is a remote object.


specifies that the remote object is a table or a view. The default is external table.


specifies that the remote object is a stored procedure.


specifies that the remote object is a file.


determines whether a remote procedure call uses the current or a separate connection. The valid values are:

  • non_transactional – is a separate connection is used to execute the RPC.

  • transactional – is the existing connection is used to execute the RPC.

The default behavior is transactional.

at pathname

specifies the location of the remote object. pathname takes the form: server_name.dbname.owner.object, where:

  • server_name (required) – is the name of the server that contains the remote object.

  • dbname (optional) – is the name of the database managed by the remote server that contains this object.

  • owner (optional) – is the name of the remote server user that owns the remote object.

  • object (required) – is the name of the remote table, view, or procedure.

column delimiter

used to separate fields within each record when accesssing flat files. The column delimiter can be up to 16 bytes long.


the column delimiter string can be any character sequencer, but if the string is longer than 16 bytes, only the first 16 bytes are used. The use of column delimiter for proxy tables mapped to anything but files results in a syntax error.


Example 1

Creates the proxy table authors:

create existing table authors
au_id       id,
au_lname    varchar (40)    NOT NULL,
au_fname    varchar (20)    NOT NULL,
phone       char (12),
address     varchar (40)    NULL,
city        varchar (20)    NULL,
state       char (2)        NULL,
zip         char (5)        NULL,
contract    bit
at "nhserver.pubs2.dbo.authors"

Example 2

Creates the proxy table syb_columns:

create existing table syb_columns
id        int,
number    smallint,
colid     tinyint,
status    tinyint,
type      tinyint,
length    tinyint,
offset    smallint,
usertype  smallint,
cdefault  int,
domain    int,
name      varchar (30),
printfmt  varchar (255)    NULL,
prec      tinyint         NULL,
scale     tinyint         NULL
at "remote1.master.dbo.columns"

Example 3

Creates 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"

Example 4

Creates a proxy table named rpc1 for the remote procedure named p1:

create existing table rpc1
column_1       int,
column_2       int
external procedure
at "SERVER_A.db1.joe.p1"


Datatype conversions

Changes by server class

Remote procedures

Encrypted columns

create existing table automatically updates syscolumns with any encrypted column metadata from the remote table. You cannot include the encrypt keyword in the column list for a create existing table command.


ANSI SQL – Compliance level: Transact-SQL extension.


create existing table permission defaults to the table owner and is not transferable.

See also

Commands alter table, create table, create proxy_table, drop index, insert, order by clause, set, update