I'm working on a data migration that requires combining rows/values from one table to update rows in another table, and I can't figure out if I need to do a nested FOREACH or something else. Here's the example.
I have a table called Health that has a unique child record, key is childID.
I have another table called Concerns that has multiple records for each child. The Concerns table structure has several Boolean fields that need to capture and retain a true value, no matter what the value is in the next record, i.e. once a field is true, it's always true. Then those values need to update the child record in the Health table.
So if the Concerns table has the following records for a child:
ChildID, DentalConcern, VisionConcern, HearingConcern.
1, True, False, False
1, False, True, False
1, False, False, False
The final values I need to update the Health table are:
1, True, True, False.
And of course, my recordset of Concerns has records for many children.
O.K., that's the background. I have Foreach Loop container set up to enumerate through the ADO recordset of the Concerns table. I have recordset variables set up for childID and each of the boolean Concerns fields. My thought was then to do a nested Foreach Loop container on the childID variable, with a Script Task to read in the recordset variables, then collect the True/False values in my readwrite variables I set up to "collect" the values of each record.
I think then I can compare the incoming recordset childID with the readwrite childID variable to see if it's changed, and if it has then I want to do the SQL update to the Health table. I'm stuck trying to figure out where to put my Execute SQL task to update the child record when I'm finished with one child. in the the Script Task. If it's in the nested Foreach, won't it execute the SQL for every record? Same question on the outer Foreach that's looping through the entire ADO recordset.
So should I put the Update sql statement in the Script Task instead of a separate Execute SQL Task?
Or is there a totally different way I need to look at looping through the entire recordset but doing processing on a subset based on the childID value?
Hope that makes sense, and thanks in advance for any help/suggestions.
Chera
cboom wrote:
I'm working on a data migration that requires combining rows/values from one table to update rows in another table, and I can't figure out if I need to do a nested FOREACH or something else. Here's the example.
I have a table called Health that has a unique child record, key is childID.
I have another table called Concerns that has multiple records for each child. The Concerns table structure has several Boolean fields that need to capture and retain a true value, no matter what the value is in the next record, i.e. once a field is true, it's always true. Then those values need to update the child record in the Health table.
So if the Concerns table has the following records for a child:
ChildID, DentalConcern, VisionConcern, HearingConcern.
1, True, False, False
1, False, True, False
1, False, False, False
The final values I need to update the Health table are:
1, True, True, False.
And of course, my recordset of Concerns has records for many children.
O.K., that's the background. I have Foreach Loop container set up to enumerate through the ADO recordset of the Concerns table. I have recordset variables set up for childID and each of the boolean Concerns fields. My thought was then to do a nested Foreach Loop container on the childID variable, with a Script Task to read in the recordset variables, then collect the True/False values in my readwrite variables I set up to "collect" the values of each record.
I think then I can compare the incoming recordset childID with the readwrite childID variable to see if it's changed, and if it has then I want to do the SQL update to the Health table. I'm stuck trying to figure out where to put my Execute SQL task to update the child record when I'm finished with one child. in the the Script Task. If it's in the nested Foreach, won't it execute the SQL for every record? Same question on the outer Foreach that's looping through the entire ADO recordset.
So should I put the Update sql statement in the Script Task instead of a separate Execute SQL Task?
Or is there a totally different way I need to look at looping through the entire recordset but doing processing on a subset based on the childID value?
Hope that makes sense, and thanks in advance for any help/suggestions.
Chera
Won't the following work:
UPDATE h
SET h.DentalConcern = c.MaxDentalConcern,
h.VisionConcern = c.MaxVisionConcern,
c.HearingConcern = c.MaxHearingConcern
FROM Health h
INNER JOIN (
SELECT ChildID,
CAST(MAX(CAST(DentalConcern as tinyint)) AS bit) as MaxDentalConcern,
CAST(MAX(CAST(VisionConcern as tinyint)) AS bit) as MaxVisionConcern,
CAST(MAX(CAST(HearingConcern as tinyint)) AS bit) as MaxHearingConcern,
FROM concerns
GROUP BY ChildID
) c
ON h.ChildID = c.ChildID
?
-Jamie
|||
Well, back to basic Transact-SQL for me. Did play with doing Max on the boolean fields which obviously didn't work, and didn't even think to Cast to integer. Many, many thanks.
Chera
