StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUpdate Statement?
text
Body
copied!<p>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 <code>[Staging.HRIS_RecruitingGL]</code>.</p> <p>Both tables are structured identical. </p> <pre><code>TABLE [dbo].[HRIS_RecruitingGL]( [FiscalYear] [varchar](50) NOT NULL, [Amount] [decimal](20, 2) NOT NULL) </code></pre> <p>The data that is in the Staging Table looks like this.</p> <pre><code>|FiscalYear|Amount| |2012 |250.25| |2013 |175.13| </code></pre> <p>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 <code>[dbo.HRIS_RecruitingGL]</code>. This way the main table will grow as the years progress.</p> <p>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)</p> <p>Here is the Merge Statement that I tried to use. </p> <pre><code>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 ); </code></pre> <p>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?</p> <p>Thank you for any assistance you can provide. </p> <p>UPDATE: I have changes the script as you suggested and I received the following Syntax error. </p> <pre><code>Msg 207, Level 16, State 1, Line 3 Invalid column name 'FiscleYear'. Msg 207, Level 16, State 1, Line 7 Invalid column name 'FiscalYear'. </code></pre> <p>I have added a screen shot so that you can see the setup of the tables as well. <img src="https://i.stack.imgur.com/BXI4x.jpg" alt="enter image description here"></p> <p>UPDATE: </p> <p>I added the Script to an SQL Task in my SSIS package. When I ran the job it returned the following error message. </p> <pre><code>[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. </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload