Problem: Copy data from server 1 to server 2

[Andy]

Hi together !

I have a problem:
I "just" wan't to copy some records from server 1 to
server 2 (same table).

But here are the problems:
1. The servers are not connected (can't use linked servers
or replication).
2. The target table already contains records, so i only
wan't to insert the new records, and update the existing
records.
3. I only wan't to copy some fields and not all records
from the source table, not all (so BCP isn't really
suitable).

What is the best way to do that ?

Regards

Andy

[Russell Fields]

Andy,

This sounds like a job for DTS. The servers do not have to be linked since
DTS runs as a client application. Look into some of DTS's simpler tools and
this should be fairly straightforward.

----

Regarding point 2: Of course, bcp only inserts data into a target table, so
you could use it if you had the rows copied from the source server.

Regarding point 3: If you create a view in your source database that
defines the rows and columns that you want, you can then use bcp to get that
data set out of the source server.

Russell Fields

"Andy" <info@axxis24.de> wrote in message
news:8cd101c3457f$f12ac120$a401280a@phx.gbl...

> Hi together !
>
> I have a problem:
> I "just" wan't to copy some records from server 1 to
> server 2 (same table).
>
> But here are the problems:
> 1. The servers are not connected (can't use linked servers
> or replication).
> 2. The target table already contains records, so i only
> wan't to insert the new records, and update the existing
> records.
> 3. I only wan't to copy some fields and not all records
> from the source table, not all (so BCP isn't really
> suitable).
>
> What is the best way to do that ?
>
> Regards
>
> Andy
>

[Andy]

Russell,

thank you for your answer.

But i guess i have still a problem, the 2 servers are at 2
different locations. There's no connection, so i have to
send the "result" per mail, the correspondig DBA has
to "exectue" something. So in my experience, DTS contains
the "schema" what to do ... but not the data, or i'm
wrong ?!

To use a view as source for BCP is of course a good idea,
but with this solution i can only insert records.
And how will you check if the record is really new in the
target table ? BCP just pumps data without checks, or ?!

What we do now:
I write

IF EXISTS (...)
UPDATE (...)
ELSE
INSERT(...)

statements by myself, but that's only "possible" with less
data, otherwise it's just to much work !

Can Enterprise Manager autom. script SQL statements like
this ? I never heard something like that. Or maybe a 3'
party tool ?

Regards,
Andy

>-----Original Message-----
>Andy,
>
>This sounds like a job for DTS. The servers do not have

to be linked since

>DTS runs as a client application. Look into some of

DTS's simpler tools and

>this should be fairly straightforward.
>
>----
>
>Regarding point 2: Of course, bcp only inserts data into

a target table, so

>you could use it if you had the rows copied from the

source server.

>
>Regarding point 3: If you create a view in your source

database that

>defines the rows and columns that you want, you can then

use bcp to get that

>data set out of the source server.
>
>Russell Fields
>
>"Andy" <info@axxis24.de> wrote in message
>news:8cd101c3457f$f12ac120$a401280a@phx.gbl...

>> Hi together !
>>
>> I have a problem:
>> I "just" wan't to copy some records from server 1 to
>> server 2 (same table).
>>
>> But here are the problems:
>> 1. The servers are not connected (can't use linked

servers

>> or replication).
>> 2. The target table already contains records, so i only
>> wan't to insert the new records, and update the existing
>> records.
>> 3. I only wan't to copy some fields and not all records
>> from the source table, not all (so BCP isn't really
>> suitable).
>>
>> What is the best way to do that ?
>>
>> Regards
>>
>> Andy
>>

>
>
>.
>

[SQL Scripter]

Hi Andy,

there's a tool which offers the functioanlity.

[url]http://www.sqlscripter.com[/url]

Enjoy.

>-----Original Message-----
>Hi together !
>
>I have a problem:
>I "just" wan't to copy some records from server 1 to
>server 2 (same table).
>
>But here are the problems:
>1. The servers are not connected (can't use linked

servers

>or replication).
>2. The target table already contains records, so i only
>wan't to insert the new records, and update the existing
>records.
>3. I only wan't to copy some fields and not all records
>from the source table, not all (so BCP isn't really
>suitable).
>
>What is the best way to do that ?
>
>Regards
>
>Andy
>
>.
>

[Russell Fields]

Andy,

Going back to the DTS solution (if you pursue it) you will need two
packages.

Package 1: On the source machine extracts from your table to (for example) a
tab-delimited file (or anything OLE DB can talk to such as an Excell file)

Email the file

Package 2: Reads from the emailed file and supports the logic check for
existence and insert of the row is new.

Russell Fields

"Andy" <info@axxis24.de> wrote in message
news:042801c3459b$130cf6d0$a501280a@phx.gbl...

> Russell,
>
> thank you for your answer.
>
> But i guess i have still a problem, the 2 servers are at 2
> different locations. There's no connection, so i have to
> send the "result" per mail, the correspondig DBA has
> to "exectue" something. So in my experience, DTS contains
> the "schema" what to do ... but not the data, or i'm
> wrong ?!
>
> To use a view as source for BCP is of course a good idea,
> but with this solution i can only insert records.
> And how will you check if the record is really new in the
> target table ? BCP just pumps data without checks, or ?!
>
> What we do now:
> I write
>
> IF EXISTS (...)
> UPDATE (...)
> ELSE
> INSERT(...)
>
> statements by myself, but that's only "possible" with less
> data, otherwise it's just to much work !
>
> Can Enterprise Manager autom. script SQL statements like
> this ? I never heard something like that. Or maybe a 3'
> party tool ?
>
> Regards,
> Andy
>

> >-----Original Message-----
> >Andy,
> >
> >This sounds like a job for DTS. The servers do not have

> to be linked since

> >DTS runs as a client application. Look into some of

> DTS's simpler tools and

> >this should be fairly straightforward.
> >
> >----
> >
> >Regarding point 2: Of course, bcp only inserts data into

> a target table, so

> >you could use it if you had the rows copied from the

> source server.

> >
> >Regarding point 3: If you create a view in your source

> database that

> >defines the rows and columns that you want, you can then

> use bcp to get that

> >data set out of the source server.
> >
> >Russell Fields
> >
> >"Andy" <info@axxis24.de> wrote in message
> >news:8cd101c3457f$f12ac120$a401280a@phx.gbl...

> >> Hi together !
> >>
> >> I have a problem:
> >> I "just" wan't to copy some records from server 1 to
> >> server 2 (same table).
> >>
> >> But here are the problems:
> >> 1. The servers are not connected (can't use linked

> servers

> >> or replication).
> >> 2. The target table already contains records, so i only
> >> wan't to insert the new records, and update the existing
> >> records.
> >> 3. I only wan't to copy some fields and not all records
> >> from the source table, not all (so BCP isn't really
> >> suitable).
> >>
> >> What is the best way to do that ?
> >>
> >> Regards
> >>
> >> Andy
> >>

> >
> >
> >.
> >

[Andy]

Russell,

thank's again for you advice.

Meanwhile i tested a recommended tool, and it exactly
offers what i need :-) Wow !

([url]http://www.sqlscripter.com[/url])

Of course, i think your solution should work too.
Thanks !

Bye
Andy

>-----Original Message-----
>Andy,
>
>Going back to the DTS solution (if you pursue it) you

will need two

>packages.
>
>Package 1: On the source machine extracts from your

table to (for example) a

>tab-delimited file (or anything OLE DB can talk to such

as an Excell file)

>
>Email the file
>
>Package 2: Reads from the emailed file and supports the

logic check for

>existence and insert of the row is new.
>
>Russell Fields
>
>"Andy" <info@axxis24.de> wrote in message
>news:042801c3459b$130cf6d0$a501280a@phx.gbl...

>> Russell,
>>
>> thank you for your answer.
>>
>> But i guess i have still a problem, the 2 servers are

at 2

>> different locations. There's no connection, so i have

to

>> send the "result" per mail, the correspondig DBA has
>> to "exectue" something. So in my experience, DTS

contains

>> the "schema" what to do ... but not the data, or i'm
>> wrong ?!
>>
>> To use a view as source for BCP is of course a good

idea,

>> but with this solution i can only insert records.
>> And how will you check if the record is really new in

the

>> target table ? BCP just pumps data without checks,

or ?!

>>
>> What we do now:
>> I write
>>
>> IF EXISTS (...)
>> UPDATE (...)
>> ELSE
>> INSERT(...)
>>
>> statements by myself, but that's only "possible" with

less

>> data, otherwise it's just to much work !
>>
>> Can Enterprise Manager autom. script SQL statements

like

>> this ? I never heard something like that. Or maybe a 3'
>> party tool ?
>>
>> Regards,
>> Andy
>>

>> >-----Original Message-----
>> >Andy,
>> >
>> >This sounds like a job for DTS. The servers do not

have

>> to be linked since

>> >DTS runs as a client application. Look into some of

>> DTS's simpler tools and

>> >this should be fairly straightforward.
>> >
>> >----
>> >
>> >Regarding point 2: Of course, bcp only inserts data

into

>> a target table, so

>> >you could use it if you had the rows copied from the

>> source server.

>> >
>> >Regarding point 3: If you create a view in your

source

>> database that

>> >defines the rows and columns that you want, you can

then

>> use bcp to get that

>> >data set out of the source server.
>> >
>> >Russell Fields
>> >
>> >"Andy" <info@axxis24.de> wrote in message
>> >news:8cd101c3457f$f12ac120$a401280a@phx.gbl...
>> >> Hi together !
>> >>
>> >> I have a problem:
>> >> I "just" wan't to copy some records from server 1 to
>> >> server 2 (same table).
>> >>
>> >> But here are the problems:
>> >> 1. The servers are not connected (can't use linked

>> servers

>> >> or replication).
>> >> 2. The target table already contains records, so i

only

>> >> wan't to insert the new records, and update the

existing

>> >> records.
>> >> 3. I only wan't to copy some fields and not all

records

>> >> from the source table, not all (so BCP isn't really
>> >> suitable).
>> >>
>> >> What is the best way to do that ?
>> >>
>> >> Regards
>> >>
>> >> Andy
>> >>
>> >
>> >
>> >.
>> >

>
>
>.
>