CODEWITHSUNDEEP
sql-servert-sqlmerge
Johannes Wentu
Johannes Wentu

Reputation: 989

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 list of (firstValue, SecondValue,...,LastValue), one for each row you want to merge but I'd like to be able to write the instruction with NO rows so that the DELETE part of the MERGE deletes all the rows.

This is because I am using a stored procedure that creates the MERGE instruction automatically but sometimes the table that i am starting from is empty.

Of course I tried with:

MERGE INTO MySchema.MyTable AS Target USING (VALUES)

but it is not accepted.

Example:

MERGE INTO [dbo].[MyTable] AS Target
USING (VALUES (1,'Attivo') ,(2,'Disabilitato') ,(3,'Bloccato') ) AS Source ([IDMyField],[MyField]) ON (Target.[IDMyField] = Source.[IDMyField])
WHEN MATCHED AND ( NULLIF(Source.[MyField], Target.[MyField]) IS NOT NULL OR NULLIF(Target.[MyField], Source.[MyField]) IS NOT NULL)
THEN UPDATE SET [MyField] = Source.[MyField]
WHEN NOT MATCHED BY TARGET
THEN INSERT([IDMyField],[MyField]) VALUES(Source.[IDMyField],Source.[MyField]) 
WHEN NOT MATCHED BY SOURCE
THEN DELETE;

Upvotes: 2

Views: 4394

Answers (2)

Johannes Wentu
Johannes Wentu

Reputation: 989

A viable solution is : 

USING (SELECT * FROM MyTable WHERE 1 = 0)

Upvotes: 5

Philip Devine
Philip Devine

Reputation: 1169

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;

Upvotes: 1

Related Questions