Best Practice - Updating a table from a view in another DB

[KCcasey]

Hi,

I’m hoping that I can get some guidance I have a table that I want to be in sync with a view in a separate (SQL2K) DB. I’ve never played with triggers, or had much to do with implementing referential integrity in SQL2K - - I’m pretty much ignorant!

There seems to be several options that I can pursue, and before I do I’d like some feedback as to the do-ability, pros, cons etc. And please tell me if I’ve missed the obvious!!

Option 1.
Create a SP to delete all rows from destination table and then repopulate based on the view. Runs to schedule.


Option 2.
Use INSTEAD OF triggers (somehow) to update my table.


Option 3.
Implement some form of ref’ integrity with keys to force updates to my destination table based on the source tables of the view.

Thanks in advance for any help!!

Kind Regards,

Casey.

 

[MartinCroft]

hi

The view you want to have a copy of has it one underlying table i.e does it just get its data from one or more tables?
if its a single table what updates it, users manually appliaction , dts etc.

 

[KCcasey]

Hi,

The view is comprised of 4 underlying table, a couple small, a couple (relatively) large.

Does this change my options?

Regards,

Casey.