My SQL query is as follows:
IF OBJECT_ID('tempdb..#OutputIds') IS NOT NULL DROP TABLE #OutputIds;
CREATE TABLE #OutputIds (Action NVARCHAR(10), ChildId NVARCHAR(MAX));
MERGE INTO LeadShirts AS target
USING (VALUES ('abc', 'abc'), ('abc', 'abc')) AS source (ParentId, ChildId)
ON (target.Lead = source.ParentId AND target.Shirts = source.ChildId)
WHEN MATCHED AND target.Deleted = 1 THEN
UPDATE SET UpdatedOn = GETDATE(), Deleted = 0 -- Re-activate deleted records
OUTPUT 'Edited', source.ChildId INTO #OutputIds (Action, ChildId)
WHEN NOT MATCHED BY TARGET THEN
INSERT (Lead, Shirts, Deleted, CreatedOn)
VALUES (source.ParentId, source.ChildId, 0, GETDATE())
OUTPUT 'Added', source.ChildId INTO #OutputIds (Action, ChildId);
INSERT INTO #OutputIds (Action, ChildId)
SELECT 'Skipped', ChildId FROM (VALUES ('abc', 'abc'), ('abc', 'abc')) AS V(ParentId, ChildId)
WHERE EXISTS (SELECT 1 FROM LeadShirts AS T WHERE T.Lead = V.ParentId AND T.Shirts = V.ChildId AND T.Deleted IS NULL);
SELECT Action, ChildId FROM #OutputIds;
SELECT Action, ChildId FROM #OutputIds;
And I keep getting this error when I run it on SQL Server:
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword ‘WHEN’.
Any solution will be greatly appreciated.