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 |
|
gal118
Starting Member
2 Posts |
 Posted - 2010-03-17 : 13:40:52
|
| Hello everyone,I load new data from one database (temp) to another (production) on weekly basis and for each table I have to do the following:Move the data that changed since the last load from the production database to the archive and update the production tables with the new data. What is the best way to do this? Thank you. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-17 : 13:54:59
|
| the best way to do this is to maintain an audit column in each of tables and for each week look for ones having datvalue occuring in elapsed week. you can link this column to defau;t constraint and trigger to make sure value gets updated for each insert/update operation. then move the deltas (changed records) to a staging table and then do insert/update/delete on production tables by comparing with pk values.1. if record with pk in production and temp, do update2. if record in temp and not in production, do insert3. if physical deletes happen,make sure you capture that info on temp to history table via trigger and then use that table to delete those records from production.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-17 : 14:03:42
|
| Or for (3) delete from Production where PK not in Temp?I wonder if that is easier / as fast as trying to log the deletes with a trigger? (You have to also take care of Delete and Recreate if you go the trigger route, whereas for compare PKs that problem is moot).Or, I suppose, do the Deletes first, and then the Insert will copy-back any that have been recreated since. |
|
|
|
gal118
Starting Member
2 Posts |
Posted - 2010-03-22 : 16:42:59
|
| Thank you, visakh16 and Kristen, for your replies. I think I might have not explained it well, but I am not sure how the audit column would work for me. Here are more details. There are 4 tables total in the temp db where the data get dropped every week and the new load is transferred from the data warehouse. From those 4 temp tables I have to transfer the data to our production tables and while doing that, I have to check what has changed in the production table compared to the temp table. For example one of the tables has patient medications info (med. record number, when the med. was first prescribed, instructions on taking it, the stop date etc.) During the data transfers I have to check if something has changed (say the stop date is not not NULL anymore because a patient has stopped taking it) and having found any changes I have to transfer the record to the archive and update it in the production table. The data in those 4 production tables never change except on the load days, that is they do not get altered in any other ways (no updates, inserts, deletes) other than by the transfers. The temp tables have composite keys consisting of 4-5 fields because originally they were set up this way that only a combination of a few fields makes a record unique. I can't modify the tables. I have written procedures that use cursor to go over the temp table record-by-record but it is obviously not a good way because with the amount of data in the production tables increasing the procedures are very slow. I guess my question is how to do it other than row-by-row way. Thank you very much again. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-23 : 10:15:10
|
| as i told earlier, if you've audit column like created/modifieddate on each table, it can really make it simple.Otherwise you might need to compare on every other fields than ones in pk to identify if the record had any change in it during the elapsed week.The presence of audit column makes sure only those records that either changed or newly got added will have date value that falls in elapsed week (by virtue of trigger/default constraint on it)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
data load question