Update Statement?

Question

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].

Both tables are structured identical.

TABLE [dbo].[HRIS_RecruitingGL](
      [FiscalYear] [varchar](50) NOT NULL,
      [Amount] [decimal](20, 2) NOT NULL) 

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)

Here is the Merge Statement that I tried to use.

MERGE dbo.HRIS_RecruitingGL AS tgt 
USING (
    SELECT  DATENAME(YEAR, GETDATE()) AS FiscleYear AND Amount,
    FROM Staging.HRIS_RecruitingGL
    ) AS rgl ON rgl.FiscalYear = tgt.FiscalYear
WHEN    MATCHED
    THEN    UPDATE
        SET tgt.FiscalYear = rgl.FiscalYear,
            tgt.Amount = rgl.Amount
WHEN    NOT MATCHED BY TARGET
    THEN    INSERT  (
                FiscalYear,
                Amount
            )
        VALUES  (
                rgl.FiscalYear,
                rgl,Amount
            );

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.

UPDATE: I have changes the script as you suggested and I received the following Syntax error.

Msg 207, Level 16, State 1, Line 3
Invalid column name 'FiscleYear'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'FiscalYear'.

I have added a screen shot so that you can see the setup of the tables as well.

UPDATE:

I added the Script to an SQL Task in my SSIS package. When I ran the job it returned the following error message.

[Execute SQL Task] Error: Executing the query "MERGE   dbo.HRIS_RecruitingGL AS tgt    
USING   (..." failed with the following error: "The MERGE statement attempted to 
UPDATE or DELETE the same row more than once. This happens when a target row matches 
more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the 
target table multiple times. Refine the ON clause to ensure a target row matches at 
most one source row, or use the GROUP BY clause to group the source rows.". Possible 
failure reasons: Problems with the query, "ResultSet" property not set correctly, 
parameters not set correctly, or connection not established correctly.

Answer 1

You can achieve this by using a MERGE instead of an Update. Merge will do an update where the keys match, and do an insert where the source key (from your staging table) is not found in the destination table.

See this page for more info on Merge. Example "A" is the type of merge that you need.

Looking at your merge, you have not included the amount field from the staging table and you are calculating the fiscal year, which means you are not using the staging table at all.

I would expect the merge to look like the following, (all I have done is changed the SELECT clause on the staging table)

MERGE   dbo.HRIS_RecruitingGL AS tgt 
USING   (
        SELECT  FiscalYear,
                Amount
        FROM    Staging.HRIS_RecruitingGL
        )       AS rgl 
        ON      rgl.FiscalYear = tgt.FiscalYear
WHEN    MATCHED
THEN    UPDATE
        SET     tgt.FiscalYear = rgl.FiscalYear,
                tgt.Amount = rgl.Amount
WHEN    NOT MATCHED BY TARGET
THEN    INSERT  (
                FiscalYear,
                Amount
                )
        VALUES  (
                rgl.FiscalYear,
                rgl,Amount
                );