The code below is successfully updating an existing record but does not insert the new record where the details do not match and I don't understand why not as the code compiles and does not throw any error messages. I'm sure I have missed something obvious. For reference I using SQL Server 2008 with a case-sensitive collation but I don't see how that makes a difference. I have other MERGE cases that work just fine, it's just this one that doesn't play nicely.
To see the update working, modify the colourid in the DEBUG variable declarations to be the same as the value in the insert statement.
BEGIN TRY
DROP TABLE #adr_test
END TRY
BEGIN CATCH
-- nothing to drop
END CATCH
CREATE TABLE #adr_test
(
style NVARCHAR(5)
,size_id INT
,colour_id INT
,cost MONEY
)
INSERT INTO #adr_test (style, size_id, colour_id, cost)
VALUES ('ADR01', 100, 101, 99.99)
/*DEBUG*/
DECLARE @style NVARCHAR(5) = 'ADR01'
DECLARE @sizeid INT = 100
DECLARE @colourid INT = 999
DECLARE @ctncost MONEY = 1.50
/*END DEBUG*/
MERGE #adr_test AS Tgt
USING (SELECT style, size_id, colour_id, cost
FROM #adr_test
WHERE style = @style
AND size_id = @sizeid
AND colour_id = @colourid) AS Src ON Src.style = Tgt.style
AND Src.size_id = Tgt.size_id
AND Src.colour_id = Tgt.colour_id
WHEN MATCHED AND Tgt.cost <> @ctncost
THEN
UPDATE SET Tgt.cost = @ctncost
WHEN NOT MATCHED BY TARGET
THEN
INSERT (style, size_id, colour_id, cost)
VALUES (@style, @sizeid, @colourid, @ctncost);
SELECT * FROM #adr_test
To elaborate on RBarry Young and Code Different's responses, NOT MATCHED compares what is in the SOURCE with what is in the target. Because I am selecting from the same table with the filter criteria, the source results are NULL so there is nothing to be NOT MATCHED. the code in the USING should look like this
This way the SOURCE will contain a results set with a single record which may or may not be found in the TARGET table. when it is not matched then the insert will be triggered.
Thanks for your help guys.