Kds4evr
asked on
SQL MERGE Statement with Target Field Variable
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
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?
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);
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?
ASKER
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"
The issue appears to be the S.TargetField in the column list which gives me an invalid column name. Thoughts?
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);
The issue appears to be the S.TargetField in the column list which gives me an invalid column name. Thoughts?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Used SSIS Solution with Conditonal Split
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.