April 24, 2015 at 2:09 am
Hi,
We are getting data feed from Oracle database in our project. Everyday we will need to track if any rows got inserted/updated/deleted in the source and get that update right into our data warehouse.
Currently we are taking a dump of the required table (as it is) to our staging DB and comparing it with previous day data to track the changes (column by column comparison). This approach is working currently but has performance bottleneck. There is no tracking column (eg. last modified date or time) in source that will give us any idea of what got changed. Also there is no identity key or primary key in the source data.
Is there a way in SQL Server to get that inserted/updated records only instead of comparing column by column to track the changes?
Thanks,
Anjan
__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!
April 24, 2015 at 6:34 am
You could set up a trigger to capture inserts. That might cause some performance hits though. You could capture queries using extended events and sort out the INSERT statements. It won't make for easy querying to get all the data inserted though. I guess it depends on if you need to capture all changes or you just want to be able to check on a spot basis. Extended events is probably the least intrusive and safest. It's just going to be harder to query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply