October 20, 2008 at 7:14 am
Hello all,
I have been asked to set up an interface which will automatically populate one database when a record is created in another database.
I have used triggers to do this before, when it has involved a single table populating to a single table, but this one is a biut more complicated.
Basically I have one SQL Server 2000 database:
"Source1" which is an HR database and is used by the HR department on a day to day basis. All new employees are manually entered via a client which creates entries on 3 separate tables when the transaction is committed. (EmployeeMaster, EmployeePayroll and EmployeePosition)
There is another server containing another SQL Server 2000 Database - "Destination1" which historically has always been used for employee data and everything was always created manually here too.
*Now they want to avoid a 2 step entry on these systems.
The problem is that the 3 source tables contain a mix of data which needs to be populated on another 2 tables (Empltable, HRMVirtualNetworkTable) and some fields need to be taken from each of the 3 source tables in order to create a record on the destination tables.
Using triggers, I cannot think of a way in which to capture all of the relevant information into one single record. By just populating the destination tables directly, I will get 3 partial records created from each trigger, since an insert is occurring on each individual table, and they each have their own associated "Inserted" temporary table.
What I would basically like to know is:
Is it possible to manage a job like this via Triggers, where the data required is stretched across multiple tables?
Is there another easier method which can be used to automatically populate information into some tables when entries are created on another table?
I did think of using an Agent job which just pulls all of the relevant information on a regular timescale, which makes more sense but would appreciate some advice and if anyone else has encountered a similar problem what sort of solution have you come up with?
October 20, 2008 at 7:30 am
I should also add that there is no timestamp column in the source database so I'm not sure how to go about defining my selection if I did have to go down the Agent Job route, incase you think I answered my own question 🙂
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply