Merge Statement - INSERT NOT EXECUTED

Transact-SQL
1

Hi.
I am having a weird issue with a merge-statement.
I am using a merge in a Stored Procedure.

	MERGE INTO T_EmployeeData as Target
	 USING (SELECT @EmployeeID as EmployeeID, @CostCenter as CostCenter) AS src
	 ON 
		Target.EmployeeID = src.EmployeeID			
	 WHEN matched	
		 and 1= 0							
		THEN update 
		set Target.Email = @Email,
		Target.DateOfBirth = @DateOfBirth,
		Target.HireDate = @HireDate				
	 WHEN NOT MATCHED 
		THEN INSERT (EmployeeID, LocationCode, PersonID, Active, FirstName, LastName, MiddleName, Gender, [Base Pay Annual Salary], Currency, ValidFrom) 			
		VALUES ('4545', 'de-gmt', '155', 1, 'sdfsdf', 'sdfsdf', 'sdfsdf', 'f', 200.25, 'EUR', getdate())

The Update will be executed (when I change 1=0 to 1=1), but the INSERT is never being executed.
The 1=0 is just for testing purposes, obviously. Also the Insert has been "minimized".

There is no error message.. nothing. OUTPUT $action, inserted.*; does not give me any hints either.
When I do change the insert to do something which is not allowed, I would receive an error message. (e.g.: Cannot insert explicit value for identity column in table 'T_EmployeeData' when IDENTITY_INSERT is set to OFF.).
But e.g. missing mandatory fields would not result in an error message.

Any idea?

2

Longshot: Trigger preventing / rolling back the INSERT?

Although "missing mandatory fields would not result in an error message" suggests that can't be the case (unless it is an INSTEAD OF trigger)

3

No triggers. When I execute the INSERT seperately, it works perfectly fine.

4

I can't see anything obviously wrong based on visual inspection either. Try starting with a simple example and work your way up to where you encounter the problem, for example something very basic as this:

CREATE TABLE #T (employeeid INT, costcenter INT);
INSERT INTO #T VALUES (1,1),(2,3);

MERGE #t AS t
USING ( SELECT 3 AS EmployeeId, 3 AS costcenter) AS s
	ON t.employeeid = s.EmployeeId
WHEN MATCHED AND 1=0 THEN
	UPDATE SET t.costcenter = s.costcenter
WHEN NOT MATCHED THEN
	INSERT (employeeId, costcenter)
	VALUES (10,10);

SELECT * FROM #t;

DROP TABLE #t;
5

Thanks to all of you!
After testing a lot I found out that it does not work to have additional comparisons after "WHEN MATCHED".
Instead I am doinf something like:

    MERGE INTO T_EmployeeData as Target
    	 USING (SELECT @EmployeeID as EmployeeID, @CostCenter as CostCenter) AS src
    	 ON 
    		Target.EmployeeID = src.EmployeeID		
               and 1= 0	
    	 WHEN matched	
    		 							
    		THEN update 
    		set Target.Email = @Email,

It really confuses me because I have used it the other way and it worked.
Anyway. Thanks!

1 Like