SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Change Tracking


Change Tracking

Author
Message
Lexa
Lexa
Say Hey Kid
Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)

Group: General Forum Members
Points: 677 Visits: 508
Hi,

We have a need to load some OLTP data into DW based on last time the data got updated/inserted. Rows in OLTP get updated sparadically, one record can get updated 0-4 times between loads and we need to pick up only records that changed/got inserted. Other than using update_date (which is a solution, but requires a lot of code changes) we are looking into implementing Change Ttracking. But from what I see, change tracking keeps track of versions for each row, which means we need to keep track of each version in DW as well to know which recrods changed by joining the two data sources. Since our DW gets loaded incrementally (every 15 minutes) those joins would be too expensive, hence the question: Is there a way to use Change Tracking without keeping track of each version for each record?



Thanks
rahulsahay123
rahulsahay123
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

Group: General Forum Members
Points: 129 Visits: 153
I guess you require this for incremental loading correct? Change Tracker is a summarized table which keep tracks of the tables with respect to the latest modified date. Simple way is to create a table which will have say 2 fields ie table name and latest_date column. Once you have calculated the delta then update this table with the max date. so always this table will have the latest date associated to the table name.

Hopefully this answers your question.


Thanks
Rahul Sahay
sqlbuddy123
sqlbuddy123
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6366 Visits: 2243
There is difference between Change Tracking and Change Data Capture. CDC stores previous versions. CT doesn't. Check if CT helps for your scenario.

http://technet.microsoft.com/en-us/library/cc280519(v=sql.105).aspx

--
SQLBuddy
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search