The INSERT command in the WHEN NOT MATCHED clause of a MERGE command is not working as expected...
MERGE dbo.MyTargetTable AS Target
USING (SELECT * FROM dbo.MySourceTable) AS Source
ON (Target.colx = Source.colx)
WHEN MATCHED
THEN UPDATE SET
Target.[ColA] = Source.[ColA],
Target.[ColB] = Source.[ColB],
WHEN NOT MATCHED
THEN INSERT
VALUES
(
Source.[ColA],
'Some String Value',
)
OUTPUT $action, Inserted.*, Deleted.*;
As you can see, I'm just trying to set ColB to a string 'Some String Value' when the record gets inserted into the target table. This works in SSMS but not in the Execute SQL Task. Do I need to do anything special to set up the MERGE command to work in SSIS? Thanks.