I want to refresh a destination or copy of a source table from a different
sql server database. I want to:

-update destination if there is a match on id between the two
-delete from destination if destination isn't in source anymore.
-insert into destination if source has a record that dest. doesn't.

Is a Data Driven Task the best approach or does SQL Server offer another
type of replication that does this? If there are links outlining this I'd
appreciate it. Thanks

Transactional replication would be a cool option here. Saying that it sounds as though you only ever want what is in the Source to
be in the destination (non updateable table) so snapshot would also fit.

A DDQ also does what you want.

If you want to have the destination do its own thing as well but not push back to the source then it becomes more complicated

How would you handle an update of a row in the source but a delete in the destination?



--

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


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

Allan,

I'm not sure how I will handle what you ask in your last question. I guess
I'm searching for a solution to that.

But yes, the destination is dependant on the source (or master) and is kind
of a snapshot of the source. The master table has 20+ columns, but I'll only
need subset of those in the destination (maybe 8) since the others are
irrelavant for this app. But for those 8 cols, I want them to match as they
are in the master source.

Or delete them if they become obsolete or insert new rows from the source if
they are added there.

Does that seem to point to one option over another in your opinion? Thanks
again.

"Allan Mitchell" wrote:

How "in Sync" do you want the table?

If it is not "Within Seconds" then all you need to do is clear down the destination and pump in the source table.



--

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


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

Allan,

Thanks. When you say data pump, do you mean the "Transfer Data Task"? The
issue I see though is I have dependant table referencing the destination so I
don't just want to delete all the records everytime because I'll have to
delete the dependant rows too.

I'll only delete a destination record (and it's dependant records) if it's
not in the source any longer which is what I'm stuck on.

If I do need the change to appear in the destination relatively quick, what
you would suggest?

Thanks again for your patience...

"Allan Mitchell" wrote:

Replication is the only real way currently of doing things in quick time.

--

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


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