Throwing exception in SQL MERGE clause is not possible

Question

I want to merge 2 tables only if all of the IDs in the source table exist in the target table. But it is not possible to throw exceptions inside merge clause. What are my alternative options to achieve the same result?

I tried this:

MERGE [dbo].[Target] as tgt
USING [dbo].[Source] as src
ON (tgt.ID = src.ID)
WHEN MATCHED THEN
    UPDATE SET tgt.Name = src.Name
WHEN NOT MATCHED THEN 
    THROW 51000, 'error: not all ids match!', 1;

I expected it to update the target table only if the source table has all the IDs there are in the target table but instead, I got an error saying:

Incorrect syntax near 'THROW'. Expecting DELETE, INSERT, or UPDATE.

Answer 1

You can't use arbitrary statements in the THEN part of a MERGE, the syntax is quite specific about what you are allowed to do.

If you want to THROW on such rows then just use a IF EXISTS... WHERE NOT EXISTS. And then convert the MERGE into a normal joined UPDATE.

Since it's now two statements, you are going to need an explicit transaction and locking hints.

SET NOCOUNT, XACT_ABORT ON;

BEGIN TRAN;

IF EXISTS (SELECT 1
    FROM dbo.Source as src WITH (SERIALIZABLE)
    WHERE NOT EXISTS (SELECT 1
        FROM dbo.Target as tgt WITH (SERIALIZABLE, UPDLOCK)
        WHERE tgt.ID = src.ID
    )
)
    THROW 50001, N'error: not all ids match!', 1;


UPDATE tgt
SET Name = src.Name
FROM dbo.Target as tgt
JOIN dbo.Source as src ON tgt.ID = src.ID;

COMMIT;

If you want the error message to tell you the first ID that fails, you could store it in a variable then check that variable for NULL.

DECLARE @message nvarchar(1000);

SELECT TOP (1)
  @message =  CONCAT(N'error: id ', src.ID, N' is not in target')
FROM dbo.Source as src WITH (SERIALIZABLE)
WHERE NOT EXISTS (SELECT 1
    FROM dbo.Target as tgt WITH (SERIALIZABLE, UPDLOCK)
    WHERE tgt.ID = src.ID
);

IF @message IS NOT NULL
    THROW 50001, @message, 1;