September 12, 2008 at 2:59 pm
I have data that will come in each night from another system (RMS) that I import to a temp table. I need to use this information to update my master table. If the row is there (both in the temp table and the master table based on an ID column) then I need to update any changed columns in that row.
If it is a new row then I need to insert it.
What is the best practice way to do this?
September 12, 2008 at 3:21 pm
If you are loading all your data into the one table then you will want to update your data first (so you don't update the new records you just inserted with the data that you just inserted :)) So, a simple outline as follows;
1. Update primary table data based on your id join condition to your temp table
2. Insert those records that are missing in the primary table but are in the temp table. Use a left join rather than doing a "not in" clause which are terrible on performance.
After that is all done you will want to truncate your temp table to prepare it for the next days load.
As you move forward you may want to make this a bit more complex so that you can handle new records that violate constraints (if you have them) or build in some scrubbing routines so that you are not updating good production data with some junk that came in with the file. All that should be considered prior to moving this into full production.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply