Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've recently been tasked with exporting data from one of our databases to a central Data Warehouse. The export is supposed to be incremental since a full load every 24 hours would be a massive waste of resources, bandwidth and time.

And therein lies my question, what is the best way to catch both new rows as well as changed rows in the database? I've seen recommendations on everything from Timestamps to creating a DateTime column and update it manually on each insert/update, and now I'm at a complete loss.

If I were to go with the DateAndTime column, I figure that there MUST be a way to create for instance a trigger or similar to catch all inserts and updates, or am I wrong?

I know that this question isn't quite as down to the nitty gritty as many other questions, but I'm really stuck and will appreciate all help and/or hints of where to look. Thanks in advance. :)

share|improve this question

1 Answer 1

up vote 2 down vote accepted

The simplest way to handle this may be a bit field like IsDirty that is updated with a trigger, and set to 0 for the whole table when you run your ETL.

If this is set to 1 for every row that is updated, and you give it a default value of 1, then you can make export views for your ETL that filter on IsDirty = 1 so you only interact with new rows.

If you need to know when data was changed and/or have multiple ETL processes hitting the same data, then you will need to have a datetime field and store export dates and times in your config table.

share|improve this answer
    
Thanks for the tip JNK, time data for changes and inserts are irrelevant. :) –  JaggenSWE Apr 24 '13 at 13:12
1  
@JaggenSWE in that case this will probably be the fastest and simplest way to handle it. A date comparison is going to be a lot slower than just checking a bit field. –  JNK Apr 24 '13 at 13:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.