Thursday, 5 September 2013

Update Statement?

Update Statement?

I have an SSIS Job that does a Weekly run every Monday. Currently the job
pulls data from a spreadsheet, runs it through an aggregation (because I
only need the annual SUM) and then puts the data into a Staging Table
[Staging.HRIS_RecruitingGL].
The data that is in the Staging Table looks like this.
|FiscalYear|Amount|
|2012 |250.25|
|2013 |175.13|
Since this report runs weekly I need the current year (and the following
years) to be updated with the job. Therefor I need a script that will pull
the data from the Staging Table and update the yearly amount on my Main
table [dbo.HRIS_RecruitingGL]. This way the main table will grow as the
years progress.
Since the Staging Table is truncated every time the Job runs I cant just
load the data straight into the main table. Starting Monday the data I
will receive will be for the current year (and future years) only, they
removed the 2012 data. But I need to keep it in my table so truncation of
the Main table is not an option (that was my original method, truncate the
table and load the new data, very simple)
What would be a script that i could use that would simply update the
current years Amount from the Staging Table and also add a new row when
the next year starts and update that information as well?
Thank you for any assistance you can provide.

No comments:

Post a Comment