Merge not inserting new values

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

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