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