SQL Server 2000 updating and inserting from one table to another?

Question

Hello,

I have a datawarehouse where I pull in data from Progress database to SQL Server 2000. I use DTS to copy the new data from a table from progress to SQL server but the problem is that some of the data are not new but updated data. So my question is how do I use the DTS to update if the data is already in there and insert if it is new data?

Another one of my plan was to insert all the previous days transaction into a temp table and use a store procedure to insert and update the datawarehouse table. which solution is better?

Answer 1

I think that you can do this using DTS but I don’t use it often enough to be sure. Importing to a temporary table will use more space but should be faster.

Answer 2

adopt the approach first extract the data from the source system...



then load it into a temporary or staging table



then validate the data...



then assign surrogate keys to the data...



then add the data into your warehouse...



how you add the data depends on how you've

categorised your requirement....



is your data static

slowly changing

or volatile...



what is the meaning / purpose of the updated data....

is it corrections or a true data/attribute change



that will govern how you store it..

Answer 3

If it have been me, Java with Hibernate, which provides a point of abstraction between the agency good judgment and the database. seem for tiered transactions interior the VB international, and in the event that they don't have it, evaluate your studying curve to get gifted with Java.