Hi!

I need to transfer data between two servers in a way that if a row exists
in the destination table, some fields are to be updated, otherwise a new
row should be inserted.
On
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_usage_6xet.asp>
it says that "For example, you can optionally insert or update records
depending on their previous existence or some other external factor.", so I
guess the data driven query task is the way to go.
However, I don't even have a clue how to do it! Can anyone get me started
please?

I've read in some previous post that Allan Mitchell recommends using a
linked server and ExecuteSql tasks to do this, but this isn't an option for
me. Is it still possible?

Thanks!
Jens

If you cannot use a linked server (only needed if servers are different)
then if the datasource is not latge you can transfer the whole of the
source to the destination then issue the correct statements. You may be
thinking that this is expensive but unless you have planned for doing what
you want to do it is going to hurt you in any event. A DDQ Will have to run
through _EVERY_ row in the source and check on the destination.
Transferring the whole table accomplishes the same thing right. If you plan
for this type of thing you could have a trigger on the source table that
logs the rows that have changed (INSERTED, UPDATED, DELETED to another
table. You would then use this as the source.

I still prefer linked server though for inter server stuff like this.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.Konesans.com


"Jens Weiermann" <spamgoeshere (AT) wexman (DOT) com> wrote

Hi Allan,

thanks for your answer! I don't want to be ungrateful, but believe me, I
*do* want to try it using DDQ. I'll only have a couple of records per day
(if any at all), so I can very well live with the performance downgrade,
and everything else like linked servers, complete table transfer etc seems
unecessary overhead to me.
My only problem is that I don't even have the slightest idea about how to
set things up to achieve this. So, any help here would be much appreciated.

Regards,
Jens

OK

Ping me by mail and I can send you a very short package and explanation of
what i would do.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Jens Weiermann" <spamgoeshere (AT) wexman (DOT) com> wrote

Hi Allan,

Allan Mitchell wrote:

thanks for your offer, but I already got it working - mostly thanks to your
excellent www.sqldts.com website - a _very_ valuable resource IMHO.

Thanks again!
Jens