CODEWITHSUNDEEP
sql-servermerge
Vinoth
Vinoth

Reputation: 2439

SQL Server 2008 Merge Statement to check and insert if a record doesnt exist

I am trying to add a record into a table by checking against certain conditions for the existance of record through sql script. I am trying it using Merge Statement. Its not working couldnt tract out where i am going wrong. Can some one help me with this?. Thanks

MERGE Categories As target
    USING (
    SELECT CategoryKey 
    From Categories where CategoryName = 'TestName'
    ) AS source on (source.CategoryKey = target.CategoryKey)
WHEN Not Matched THEN
    INSERT VALUES ('TestName');

Upvotes: 2

Views: 1987

Answers (2)

Diego
Diego

Reputation: 36136

What exactly are you trying to accomplish?

I ask because you are using the same table as target and as source and since you are comparing the CategoryKey, which I assume is the primary key, the WHEN Not Matched condition will never be evaluated, because if you have a table with IDs 1, 2 and 3 and compare against itself where ID=ID, all rows will evaluate to true.

try to change WHEN Not Matched THEN to WHEN Matched then UPDATE and write a simple test update sop you can see the behavior I'm talking about

EDIT try this (assuming your ID is auto-increment)

MERGE Categories As target
    USING (
    SELECT CategoryKey 
    From Categories where CategoryName = 'TestName'
    ) AS source on (source.CategoryName = target.CategoryName)
WHEN Not Matched THEN
    INSERT VALUES ('TestName');

Upvotes: 2

AdaTheDev
AdaTheDev

Reputation: 147224

This'll do the job:

MERGE Categories As target
USING (SELECT 'TestName' AS CategoryName) AS source 
    ON (source.CategoryName = target.CategoryName)
WHEN NOT MATCHED THEN
    INSERT (CategoryName) VALUES ('TestName');

Upvotes: 2

Related Questions