Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AdamWest
Constraint Violating Yak Guru
360 Posts |
 Posted - 2011-07-01 : 09:36:31
|
| Hi We have data that comes in from our supplier daily. This has to do with our purchasing.Currently, all data is fully refreshed daily.What are some concerns if we were to get only the changed data? alot of data is master file type data which doesn't change as well as a years worth of transactions which also have little changes per day.How is the best way to do this? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-01 : 09:42:44
|
| Make sure you keep all the data so you can always recreate.Otherwise this sounds like a good thing.Import into a staging table then merge with your current data.Put an inserted and updated date on the tables and log the number of rows in the staging table and inserted and updated so you can track performance and rows affected.I would probably have mplemented your full refresh like this anyway to see what was being changed.Do you have to deal with deletes?You also might want to consider an audit trail. It's handy even if you don't keep much history.Keep the import separate from the update. Update shuold be stored proceures. Import whatevers best - and might change in the future.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
AdamWest
Constraint Violating Yak Guru
360 Posts |
Posted - 2011-07-01 : 09:55:52
|
| About deletes, it is possible. Could you have a record id number then and work it that way? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-01 : 10:13:51
|
| You are only getting changed data so there must be something there to indicate a delete and you must have a row identifier delivered which you will have to retain to action the delete. Might want to have a deleted flag rather than actually delete.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Get refreshed daily only changed data