CODEWITHSUNDEEP
sql-servert-sql
90abyss
90abyss

Reputation: 7337

TSQL Merge: When not matched by target then insert if (condition)

I have a target table T and a source table S.

When there is a row in S that is not present in T then I want to insert it in T only when some condition is true.

This is my code so far:

merge TargetTable as target
using SourceTable as source on (source.Id = target.Id)

when not matched by target then 
    --how to do this:
    -- if exists (source.Name) then delete from source and later perform this insert
    insert ([Id], [Name])
    values (source.[Id], source.[Name])
    ;

name column is the primary key for target table. So if I run the above query then I'm getting a primary key violation error. Therefore first I need to remove the existing name in target before inserting the same name with new id.

How can I achieve this?

Upvotes: 2

Views: 1894

Answers (1)

MnM
MnM

Reputation: 306

In the MERGE statement since you are checking the condition TARGET.Id = SOURCE.Id and the same Id may not exist in the TARGET table when you want to delete the Name before inserting. So, can you try executing this MERGE statement first which checks if the [Name] exists in the TARGET table and deletes it and then execute your MERGE statement ? 

MERGE TargetTable AS TARGET
USING SourceTable AS SOURCE
ON (TARGET.Name = SOURCE.Name)
WHEN MATCHED THEN 
DELETE;
GO
MERGE TargetTable AS TARGET
USING SourceTable AS SOURCE
ON (TARGET.Id = SOURCE.Id)
WHEN NOT MATCHED BY TARGET THEN
INSERT (Id,Name)
VALUES(SOURCE.Id,SOURCE.Name);

Upvotes: 3

Related Questions