Hi All

I wonder if you could give some help and advice on the best way of
going about the following.

I want to transform and copy/import most of the data from an existing
database to an new database on a regular basis(on the same SQL 2000
server).

I have experimented and successfully set this up in DTS. The problem I
have is that the ‘datapump', imports ALL the data all of the time so I
end up with lots of repeated entries for the same data. I need to be
able to transfer only the changes into the new data tables (once the
initial import is completed) on a regular basis (perhaps half a dozen
times a day).

I have set up and tried the tutorial on the ‘sqldts' site. Must admit
that I did not fully understand the last parts of this tutorial using
global variables!.

Are look ups the best approach? Or can anyone suggest a better
alternative


Help and advice appreciated.

Lookups may be the approach but they may kill you for performance.
Can you identify changed data?
Can you use a linked server and compare PK values?

If you cannot identify changed rows and have no way of trapping the changes
(Triggers) then you are in a pickle. Whilst comparison of PK values on each
side of the data divide will tell you INSERT and DELETE activity it will not
tell you about UPDATEs



--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Mark V" <m.vyse (AT) ntlworld (DOT) com> wrote

If the two databases are on the same SQL server (even if they are not, as
long as we can establish a linked server), I would avoid using DTS to do the
actual data transfer. I would create stored procedure(s) to move the data -
code will be maintainable and easily modifiable. The performance will also be
much better than DTS. Use DTS to schedule the running of the stored
procedure.

Rangarajan
*********


"Allan Mitchell" wrote:

In one sentence you say do not use DTS to do the data transfer and in the
next you say use DTS to do the scheduling

If you are going to use a stored proc and this is the only piece of work
then why even bother with DTS to do the scheduling. ++ DTS is not a
scheduler. DTS still has to be invoked by Agent and in this case you may as
well put the proc in a Job step.



--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Rangarajan" <Rangarajan (AT) discussions (DOT) microsoft.com> wrote

I stand corrected. I was focusing on "not using DTS" to do the data transfer
in an all-SQL environment. You are right - the stored proc is to be scheduled
as a job step in SQL Agent.

Rangarajan
*********

"Allan Mitchell" wrote: