Solved

SQL MERGE Statement with Target Field Variable

Posted on 2013-12-02
5
508 Views
Last Modified: 2016-02-10
Hey Experts,

I am stuck on a SQL Merge Procedure that I am not even sure is possible. Basically, I have a table that stores field name references based on a few criteria. I have another table with fields Score0 through Score9. I have yet a third table with a list of values in a single column that is updated via an SSIS package.

What I need to do is create a Merge Statement (as SSIS does not appear to be able to do what I need it to do). The trick here is the field is dynamic based on the criteria selected. AN example of what I want is below, but the syntax is wrong.

1. Is this possible
2. I do not really care if this is done in SSIS or in a Stored Procedure called from the SSIS Package, I am just looking to automate all of this as the package updates hourly and I cannot manually feed variables in as the criteria evolves.

Stored Procedure
DECLARE @DestField AS varchar(6)

MERGE dbo.MasterScore AS T
USING dbo.QMCMScores AS S
ON (T.CCQ = S.CCQ AND T.Session = S.Session AND T.EMPLID = S.EMPLID)
WHEN NOT MATCHED BY TARGET THEN
         INSERT(CCQ,Session,EMPLID,@DestField)
         VALUES(S.CCQ,S.Session,S.EMPLID,S.Score);

Open in new window


I know I need to SET @DestField with the following (SELECT TargetField FROM dbo.ScoreLookup WHERE [System] = S.System AND CCQ = S.CCQ AND AssessmentID = S.AssessmentID) but I can't seem to figure out where this would go, or it would even work?
0
Comment
Question by:Kds4evr
  • 3
  • 2
5 Comments
 
LVL 8

Expert Comment

by:virtuadept
ID: 39692717
Instead of USING dbo.QMCMScores as S, try making that a subselect join:

USING (
   SELECT blah blah blah FROM dbo.QMCMScores as s1
   INNER JOIN dbo.ScoreLookup as s2
   ON s1.System = s2.System
   AND s1.CCQ = s2.CCQ
   AND s1.AssessmentID = s2.AssessmentID ) as S

If sub select is not allowed there (I'm not sure) then you could do that as a temp table above the MERGE statement and use temp table there.


Also replace "blah blah blah" with the actual field names you want to pull into the join that you're going to want to merge.
0
 

Author Comment

by:Kds4evr
ID: 39693440
Hey virtuadept,

Thanks for the reply. I understand the approach with the sub-query. I guess the issue lies with how to reference the returned TargetField in the INSERT statement. Currently I receive an error message "The insert list used in the MEREGE statement cannot contain multi-part indetifiers. Use single part identifiers instead"

MERGE dbo.MasterScore AS T
USING (SELECT S1.[System], S1.CCQ, S1.Session, S1.AssessmentID, S1.EMPLID, S1.Score, S2.TargetField FROM dbo.QMCMScores as s1
   INNER JOIN dbo.ScoreLookup as s2
   ON s1.System = s2.System
   AND s1.CCQ = s2.CCQ 
   AND s1.AssessmentID = s2.AssessmentID ) AS S
ON (T.CCQ = S.CCQ AND T.Session = S.Session AND T.EMPLID = S.EMPLID)
WHEN NOT MATCHED BY TARGET THEN
         INSERT(CCQ,Session,EMPLID,S.TargetField)
         VALUES(S.CCQ,S.Session,S.EMPLID,S.Score);

Open in new window


The issue appears to be the S.TargetField in the column list which gives me an invalid column name. Thoughts?
0
 
LVL 8

Expert Comment

by:virtuadept
ID: 39708851
If it says it can not use multi-part identifiers try using an "AS SomeColName" after each column in the SELECT part of the sub-select. Maybe it needs named identifiers in the sub-select?
0
 

Accepted Solution

by:
Kds4evr earned 0 total points
ID: 39719468
Yeah, I tried that as well with the same result. It just does not seem to like a variable in there in either case. I was able to do something similar using an SSIS Conditonal Split for each column. Not pretty, but it works for now. Thanks for your help.
0
 

Author Closing Comment

by:Kds4evr
ID: 39728788
Used SSIS Solution with Conditonal Split
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now