September 30, 2015 at 6:20 pm
hi,I have experience with SQL queries, but never got exposure about loading data.
We get weekly extract of data in excel. Anyone please help me what are the ways to load the data into sql?
1) like maintaining history
2) or else delete the old jobs for which new entries are there.
I know SSIS and created a package where it deleted the data in old table and inserts data from new file..But, I want to know how to manage history.
Not sure what is the process. Please help. Thanks!
November 18, 2015 at 3:43 pm
In SQL SERVER 2008 and above you have the MERGE statement. Unfortunately that does not apply to your version of SQL SERVER. For this you should stick with the three statements for changing table content across batches. These are INSERT, UPDATE, DELETE. Have a staging table with the newer data set. Do joins on the that to the permanent dataset and depending on the condition, apply one of the statements above.
EX/// If updating an existing record your data sets is defined by
stageTable inner join permanentTable on join columns...
update t
set t.mycolumn = s.mycolumn, t.updateDateTime = getdate() /*can do for all your columns */
from stageTable as s inner join permanentTable as t on t.keyField= s.keyField /* both the same of course */
A insert would pull the columns resulting from stageTable left joining on permanentTable where the permanentTable keyfield is null (because it does not exist in there yet).
For the history portion of your question... You can add triggers to the table to handle Inserts and Deletes and append those changed, deleted rows to a history table similar in structure. Have date inserted or similar audit columns in there.
Hope this helps you get started.
----------------------------------------------------
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply