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.


create existing table table_name (column_list) 
	[ on segment_name ]
	[ [ external {table | procedure | file} ] at pathname ]



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.

at pathname

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


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

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

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


SQL92 – 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