Merge not inserting new values

Question

I'm trying to use MERGE to insert new values to a table only if they don't already exists in the same table.

This is the query I am using:

MERGE [dbo].[TARGET_TABLE] AS Target 
USING 
(SELECT [NAME]
    FROM [dbo].[TARGET_TABLE] 
    WHERE [NAME]='ThisValuesDoesntExists' AND [STATUS] IS NULL) AS Source
    ON Target.[NAME]= Source.[NAME]
WHEN NOT MATCHED
    THEN INSERT ([NAME],[file_first_upload],[upload_date])
        VALUES('ThisValuesDoesntExists',1,DEFAULT);

But when I execute it, I get a (0 rows affected) message.

If I execute the "Source" query, I get 0 rows.

SELECT [NAME] 
FROM [dbo].[TARGET_TABLE] 
WHERE [NAME] = 'ThisValuesDoesntExists' AND [STATUS] IS NULL

What am I doing wrong?

Thanks

Answer 1

If you look at the MERGE documentation, you will see that the source data must exist in order to match (or not match) against existing rows in the target table:

WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>

Specifies that a row is inserted into target_table for every row returned by <table_source> ON <merge_search_condition> that doesn't match a row in target_table, but satisfies an additional search condition, if present. The values to insert are specified by the <merge_not_matched> clause. The MERGE statement can have only one WHEN NOT MATCHED [ BY TARGET ] clause.

The problem you're facing is that your "source" data is not returning anything and so the MERGE query has nothing to match against or insert.

Sample code below to demo:

IF OBJECT_ID('dbo.TARGET_TABLE', 'U') IS NOT NULL DROP TABLE dbo.TARGET_TABLE 
GO 

CREATE TABLE TARGET_TABLE ([Name] VARCHAR(100), file_first_upload BIT, upload_date DATETIME, [STATUS] VARCHAR(100)) 

MERGE [dbo].[TARGET_TABLE] AS Target 
USING 
(SELECT [NAME]
    FROM [dbo].[TARGET_TABLE] 
    WHERE [NAME]='ThisValuesDoesntExists' AND [STATUS] IS NULL) AS Source
    ON Target.[NAME]= Source.[NAME]
WHEN NOT MATCHED
    THEN INSERT ([NAME],[file_first_upload],[upload_date])
        VALUES('ThisValuesDoesntExists',1,DEFAULT);

SELECT * 
FROM TARGET_TABLE 

MERGE [dbo].[TARGET_TABLE] AS Target 
USING (VALUES ('ThisValuesDoesntExistss',1,GETDATE())) AS Source ([Name], [file_first_upload],[upload_date])
ON Target.[NAME] = Source.[Name] 
WHEN NOT MATCHED
    THEN INSERT ([NAME],[file_first_upload],[upload_date]) VALUES (Source.[Name], Source.file_First_upload, Source.upload_date);

SELECT * 
FROM TARGET_TABLE