SQL Server Merge WHEN NOT MATCHED clause customizations

Question

When merge clause is used in SQL Server, I need to insert a row when it is not available. This is what I have tried:

 drop table test;
create table test (col1 int, col2 varchar(20));
insert into test values(1, 'aaa');
insert into test values(2, 'bbb');
insert into test values(3, 'ccc');
--insert into test values(4, 'eee');

merge test as target
using (SELECT * from test where col1=4) as source
on (target.col1 = source.col1)
when matched then
    update set target.col2='ddd'
when not matched by target then
    insert values (4, 'ddd');

This updates when upon matching but fails to insert. I have got two questions:

1. Is there a way to insert upon not matching in the above case?

2. Can I customize the not matching criteria to raise an error?

Thanks.

Answer 1

The merge works, it's just that your source (SELECT * from test where col1=4) is empty. There is no such row.

You can raise an error using this hack. For example:

when not matched by target then
insert values (0/0 /*ASSERT*/, NULL);