SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Update Statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 09/05/2013 :  10:31:11  Show Profile  Reply with Quote
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.

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 09/05/2013 :  10:46:01  Show Profile  Reply with Quote
Sounds like what you're looking for is a MERGE statement. update the matching (by year) rows and insert where not matched (by year). Is that right?

Be One with the Optimizer
TG
Go to Top of Page

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 09/05/2013 :  10:48:22  Show Profile  Reply with Quote
That is correct. I tried to recycle a Merge statement from another job but i could not get it to work.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 09/05/2013 :  10:51:39  Show Profile  Reply with Quote
see if this works:

Merge into [dbo.HRIS_RecruitingGL] as trg
using [Staging] src
       on src.FiscalYear = trg.FiscalYear

when matched and trg.fiscalYear != src.FiscalYear
then update set trg.FiscalYear = src.FiscalYear

when not matched by target
then   insert (FiscalYear, Amount)
       values (src.fiscalYear, src.Amount)
;


Be One with the Optimizer
TG
Go to Top of Page

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 09/05/2013 :  10:58:57  Show Profile  Reply with Quote
Here is the Merge Statement that I tried to use.

MERGE	dbo.HRIS_RecruitingGL AS tgt
USING	(
		SELECT	DATENAME(YEAR, GETDATE()) AS FiscleYear,
		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
				);


Brian D. Brubaker
Business Intelligence Analyst
Viega LLC
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 09/05/2013 :  11:11:09  Show Profile  Reply with Quote
you're missing [Amount] in your derived table (after the comma)

Be One with the Optimizer
TG
Go to Top of Page

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 09/05/2013 :  11:21:04  Show Profile  Reply with Quote
I added [Amount] after the comma in the SELECT statement and it still gives me an "incorrect syntax near the keyword FROM" error.

I think I have written the select statement wrong.
Here is the original script I tried to alter. (for reference)


MERGE	dbo.HRIS_TORateFY AS tgt
USING	(
		SELECT	DATENAME(YEAR, GETDATE()) AS [Year],
			SUM(CASE WHEN EmpStatusName = 'Active' THEN 1 ELSE 0 END) AS HistoricalHC,
			SUM(CASE WHEN EmpStatusName = 'Withdrawn' AND TermYear = DATENAME(YEAR, GETDATE()) THEN 1 ELSE 0 END) AS NumbOfTermEE
		FROM	dbo.HRIS_EEMaster
		WHERE	ChangeStatus = 'Current'
			AND EmpStatusName IN ('Active', 'Withdrawn')
	) AS src ON src.[Year] = tgt.[Year]
WHEN	MATCHED
		THEN	UPDATE
			SET	tgt.HistoricalHC = src.HistoricalHC,
				tgt.NumbTermedEmp = src.NumbOfTermEE
WHEN	NOT MATCHED BY TARGET
		THEN	INSERT	(
					[Year],
					HistoricalHC,
					NumbTermedEmp
				)
			VALUES	(
					src.[Year],
					src.HistoricalHC,
					src.NumbOfTermEE
				);


Brian D. Brubaker
Business Intelligence Analyst
Viega LLC
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 09/05/2013 :  11:26:35  Show Profile  Reply with Quote
please post the actual (and complete) statement you are getting the syntax error with.

Be One with the Optimizer
TG
Go to Top of Page

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 09/05/2013 :  11:45:15  Show Profile  Reply with Quote
Here is the statement I used that returned the Syntax Errors.

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
                );


it returns the following syntax errors.

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


UPDATE:
I just ran it again and it returned the following error message.

Msg 110, Level 15, State 1, Line 1
There are fewer columns in the INSERT statement than values specified in the VALUES
clause. The number of values in the VALUES clause must match the number of columns
specified in the INSERT statement.



Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

Edited by - brubakerbr on 09/05/2013 11:47:45
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 09/05/2013 :  11:59:12  Show Profile  Reply with Quote
You've got a comma here instead of a period:

        VALUES  (
                rgl.FiscalYear,
                rgl,Amount
                );


Be One with the Optimizer
TG
Go to Top of Page

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 09/05/2013 :  12:03:25  Show Profile  Reply with Quote
I was just posting that. It ran successfully when I made the change.
Thank you for all of your help.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC
Go to Top of Page

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 09/05/2013 :  12:28:32  Show Profile  Reply with Quote
UPDATE: I dded the script to an SQL Task in my SSIS package.
It returned the following error message when I ran the Job.


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


Brian D. Brubaker
Business Intelligence Analyst
Viega LLC
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 09/05/2013 :  13:33:10  Show Profile  Reply with Quote
You need to make sure that your source (derived table) has the same "logical key" as the Primary Key (or unique index) of your permanent table. what I mean by logical key is that your query for the derived table should return only one row per primary key value in the permanent table. Based on your description it sounds like there should be just one row per [FiscalYear] in each table.

Be One with the Optimizer
TG
Go to Top of Page

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 09/05/2013 :  15:58:12  Show Profile  Reply with Quote
This issue is corrected now. I made a rookie mistake and forgot to truncate the staging table. Thank you for all of your support.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 09/05/2013 :  16:20:29  Show Profile  Reply with Quote
you're welcome

Be One with the Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000