Hi everyone,
I’m looking for any recommendations or ideas for achieving a task I am currently doing in a much more efficient way or form. I am currently running a process that takes data in an Excel Worksheet and populates a SQL Table. Each record within that SQL Table is then read by another system to perform workflow related tasks. The problem I am having is reading each SQL record all the time, looking for delta changes. As you can imagine, this can be pretty time consuming if each record contains many column values with thousands of records in the DB.
I’m using a DTS Package to transform the data from Excel to SQL. The package is extremely dumb, in a sense all it does before doing a bulk import is running a “TRUNCATE TABLE [table]” command against the target to clear all the values before reloading.
I created a trigger in my primary table, which listens for delta updates. Upon a record update, it makes the record change within the primary table, additionally writing that record value to another Delta_Table within the Database. The benefit there is my outside system only needs to read the delta table to make updates rather than the primary which eliminates the need to parse through each record one by one, all the time. As you see, this cuts the read time from my outside system more than half.
I guess what I’d like to do is see if there is a better way to load the table without having to truncate all the records. Ideas?
Thanks Everyone!
Can you use Integration services. If so there is a slowly changing dimension component that you set to compare the loading data against the stored data and then decide what to do.
You could load your data into another table then do the following,
DELETE any records that exist in your target table that don't exist in the laoded table
UPDATE any records that exist but are different
INSERT any new records
you can then have a trigger that populates your diff table.
I prefer the Integration services option, I believe something similar is available in DTS
No comments:
Post a Comment