Append Between Two SQL Server DB's

[HenryE]

I have two SQL Server 2000 databases on one server, call them DB1 and DB2. I would like DB1 to send data to DB2 from particular fields, whenever new information is added to those fields. So if a user adds a new person in DB1, therefore adding new data in the Names and EmployeeNumber fields in DB1, these additions need to show up in the Names and EmployeeNumber fields in DB2.

What is the best way to do this? Would it be using DTS, or triggers, or something else? It would be great if this could be done in real-time, but perhaps that would cause problems for DTS.

Thanks much.

Henry

 

[nigelrivett]

If you want it done in real time don't use dts.
Just put a trigger on the table with an insert.

create trigger tr on tbl for insert
as

insert otherdb..tbl
select EmployeeNumber, Names
from inserted

Note though that if the insert in the other database fails then the whole operation will fail. Also it will affect your backup strategy - you cannot restore one database without the other unless you have a process to keep them in step.

Better is for the trigger to insert the data into a staging table with an identity on the source database, a scheduled task then adds the data into the other database and saves the last identity transferred. Then if the other database is restored or has a problem it will automatically catch up - you will have to cater for the source being restored. This will be max a couple of minutes behind if that is allowable.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[HenryE]

nigelrivett,

Thanks for the info. Triggers it is. I actually have to link several different databases, so the real-time linkages will be with triggers and the others with DTS.

Henry