Is this possible with DTS?

12 Aug 2005 8:24 AM

bozzzza@lycos.co.uk

I would like to transfer data from one sql server to another based on
what records exist on server.

One server would be a local server (server1), the other one remote
(server2).

Is it possible to do something like this :-

select *
from server1.database1.table
where customerid not in
(select cusid from server2.database1.table)

Is this possible using DTS? If so can someone point me the right
directection.

TIA

Simon.

12 Aug 2005 8:31 AM

R.D

Possible solutions:

1.Linked server ( you can use that syntax)
2.Bulk insert( just for inserting)
3. Openquery, opendatasource,openrowset (for distributed databases)(pass
through - for adohoc)
4.dts( gives you full control for permenant use)

see BOL for this

r.d

Show quote

"bozz***@lycos.co.uk" wrote:

> I would like to transfer data from one sql server to another based on
> what records exist on server.
>
> One server would be a local server (server1), the other one remote
> (server2).
>
> Is it possible to do something like this :-
>
> select *
> from server1.database1.table
> where customerid not in
> (select cusid from server2.database1.table)
>
> Is this possible using DTS? If so can someone point me the right
> directection.
>
> TIA
>
> Simon.
>
>

12 Aug 2005 8:35 AM

Hari Prasad

Hi,

This can be done if you have Linked server created from Server1 pinting to
Server2. But if you have Linked server then
you could directly use the INSERT statement rather than DTS.

Some thing like below:-

INSERT INTO TABLE select * from server1.database1.table where customerid not
in (select cusid from server2.database1.table)

Thanks
Hari
SQL Server MVP

<bozz***@lycos.co.uk> wrote in message
Show quote

news:1123835051.556270.44930@g14g2000cwa.googlegroups.com...
>I would like to transfer data from one sql server to another based on
> what records exist on server.
>
> One server would be a local server (server1), the other one remote
> (server2).
>
> Is it possible to do something like this :-
>
> select *
> from server1.database1.table
> where customerid not in
> (select cusid from server2.database1.table)
>
> Is this possible using DTS? If so can someone point me the right
> directection.
>
> TIA
>
> Simon.
>

12 Aug 2005 8:57 AM

Enric

bozzza,
You can build a stored procedure which creates a link, do the transference
and at the end, delete the link.

Show quote

"Hari Prasad" wrote:

> Hi,
>
> This can be done if you have Linked server created from Server1 pinting to
> Server2. But if you have Linked server then
> you could directly use the INSERT statement rather than DTS.
>
> Some thing like below:-
>
> INSERT INTO TABLE select * from server1.database1.table where customerid not
> in (select cusid from server2.database1.table)
>
> Thanks
> Hari
> SQL Server MVP
>
>
> <bozz***@lycos.co.uk> wrote in message
> news:1123835051.556270.44930@g14g2000cwa.googlegroups.com...
> >I would like to transfer data from one sql server to another based on
> > what records exist on server.
> >
> > One server would be a local server (server1), the other one remote
> > (server2).
> >
> > Is it possible to do something like this :-
> >
> > select *
> > from server1.database1.table
> > where customerid not in
> > (select cusid from server2.database1.table)
> >
> > Is this possible using DTS? If so can someone point me the right
> > directection.
> >
> > TIA
> >
> > Simon.
> >
>
>
>