Merge Into With No Rows

Is there a way to use the instruction: MERGE INTO MySchema.MyTable AS Target USING (VALUES ........ ) With nothing instead of the dots? Usually you have there something like a lis

Solution 1:

A viable solution is :

USING (SELECT * FROM MyTable WHERE 1 = 0) 

Solution 2:

If you're generating the inside query, and the outside query is matching on an predefined ID field, the following will work:

MERGE INTO tester AS Target
USING (

select null as test1 --generate select null, alias as your id field
) as SOURCE on target.test1 = source.test1
WHEN NOT MATCHED BY SOURCE 
    THEN DELETE;

For your particluar case:

MERGE INTO table1 AS Target
USING (

values(null)
) as SOURCE(id) on target.id = source.id
WHEN NOT MATCHED BY SOURCE 
    THEN DELETE;