This process sets up a proxy table so that you can select and insert into a remote table on another server from your local server. It’s more elegant than doing regular bcp’s

The following example defines the remote Adaptive Server table authors, starting with the server definition:

1.         Define a server named SYBASE. Its server class is ASEnterprise, and its name in the interfaces file is SYBASE:

exec sp_addserver SYBASE, ASEnterprise, SYBASE

2.         Define a remote login alias. This step is optional. User “sa” is known to remote server SYBASE as user “sa,” password “timothy”:

exec sp_addexternlogin SYBASE, sa, sa, timothy

3.         Define the remote authors table:

create existing table authors

(

au_id      id              not null,

au_lname   varchar(40)     not null,

au_fname   varchar(20)     not null,

phone      char(12)        not null,

address    varchar(40)     null,

city       varchar(20)     null,

state      char(2)         null,

country    varchar(12)     null,

postalcode char(10)        null

)

EXTERNAL TABLE at “SYBASE.pubs2.dbo.authors”

4.         Update statistics in tables to ensure reasonable choices by the query optimizer:

update statistics authors

5.         Execute a query to test the configuration:

select * from authors where au_lname = ‘Carson’