Merge Into with no rows

Question

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;

Please post an instance of the entire MERGE statement you are using that *is* working, i.e., where the USING clause has some values. (I'm asking because I don't see anything in the MERGE documentation that allows the keyword VALUES inside the USING clause.) — Richard II, Jun 19 '15 at 12:48
If you just want to delete why are you not using a delete statement? — Sean Lange, Jun 19 '15 at 13:57
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; — Johannes Wentu, Jun 22 '15 at 06:23
I am sorry i can't add linebreaks even using the two final spaces as a like stated in the help... — Johannes Wentu, Jun 22 '15 at 06:28
@SeanLange: OF COURSE I don't "just" want to delete otherwise yes, i would have used a DELETE. Your comment is like "if you want to move on wheels why don't you use a bicycle instead of an airplane?" -> because I ALSO want to fly and a bicycle goes on wheels but does NOT , in general, allow me to fly. What i need is a full MERGE that can handle also the case in which the source table has NO rows and i need the target table to have no rows but i also want to be able to update and insert: this is what a MERGE should be intended for. — Johannes Wentu, Jun 22 '15 at 06:32
@JohannesWentu no need to be snarky here. Your question provides very limited information and from reading it again today it doesn't read like a MERGE is what you need. To be honest, I would shy away from using MERGE as there are quite a few bugs with it. http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ — Sean Lange, Jun 22 '15 at 13:41
@JohannesWentu my solution solves the question, if it didn't answer your question that's fine. But if it helped provide you with the solution then accepting it would be a common courtesy. Thanks and good luck! — Philip Devine, Jun 22 '15 at 17:54
Thank you for your answer Philip but it didnt solve my problem. I need to use the construct USING(VALUES (...), (...),...) in the merge as this is the construct used in the Stored Procedure "sp_generatemerge" that is found here: https://github.com/readyroll/generate-sql-merge/blob/master/master.dbo.sp_generate_merge.sql . I need to understand how to correct this script to make it work in case a table is empty — Johannes Wentu, Jun 23 '15 at 10:10
SQL, unfortunately, makes several "mistakes" that don't make sense from a relational purists point of view. Unfortunately, here's one of them - SQL doesn't think that tables with no columns makes sense and so doesn't allow them. — Damien_The_Unbeliever, Jun 23 '15 at 14:34

score 4 · Answer 1 · edited Sep 21 '16 at 21:02

4

A viable solution is :

USING (SELECT * FROM MyTable WHERE 1 = 0)

edited Sep 21 '16 at 21:02

Petter Friberg

21,252
9
60
109

answered Sep 21 '16 at 20:53

Johannes Wentu

931
1
14
28

Philip Devine · Accepted Answer · 2015-06-23T14:25:21.567

1

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;

edited Jun 23 '15 at 14:25

answered Jun 19 '15 at 12:55

Philip Devine

1,169
5
11

ops... wait... it seemed to work with a simple table with just one nullable column . But when i tried it with a normal table it doesn't seem to work because the PK of the table is, of course, not nullable. Am I wrong? – Johannes Wentu Jun 24 '15 at 08:39
1

After much trying and thankx to your first answer I think a viable solution is something like: USING (SELECT * FROM MyTable WHERE 1 = 0) – Johannes Wentu Jun 24 '15 at 08:58
@JohannesWentu you should add that as a separate answer because it's better than the two suggestions here. – davmos Sep 21 '16 at 16:01

Merge Into with no rows

2 Answers2

Linked