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.