Synchronizing Two Database Tables Periodically

[Cheryl3D]

Hi:
I have a problem to resolve regarding a table named UIC that is located in two separate SQL Server 2000 databases called 'DatabaseD' and 'DatabaseN'. Both of these databases are on the same server.

I want the UIC table in 'DatabaseN' to be synchronized with the UIC table in 'DatabaseD'-- so that any time the users add a new entry to a field called 'UIC_DESCR' in 'DatabaseD' the trigger on the UIC table in 'DatabaseN'will insert the same entry in its UIC table.
I thought that I might start by creating a trigger on 'DatabaseN' that looks like the following:

USE DatabaseD
CREATE TRIGGER checkUIC
ON UIC
AFTER INSERT, UPDATE
AS
IF UPDATE(UIC_DESCR)
--Remember the UIC_DESCR has already been inserted
INSERT INTO DatabaseN.dbo.UIC
WHERE UIC_DESCR IN (SELECT UIC_DESCR FROM DatabaseD.dbo.inserted)

Is this correct? Is this the best way to perfrom this task? Could DTS be used instead, and if so, please provide an coded example?

Any assistance would be greatly appreciated.

Thanks,
Cheryl

 

[TheBugSlayer]

Not sure if it is the best but definitely an option. DTS will introduce a higher delay. Transactional replication is your other options...Though now I don't quite remember if you can subscribe to a publication on the same server.