CODEWITHSUNDEEP
sql-serversql-server-2012
Tony_Henrich
Tony_Henrich

Reputation: 44085

How to use MERGE's 'When not matched' to insert a new record in same table?

I am trying to do an upsert into a single table using SQL Server's Merge statement.

declare @Mergeoutput table (action varchar(50))

declare variables ...

MERGE Usertable AS target
using (Select .... from Usertable where filter conditions using variables)
    as Source (column names..)
on source.... = target.... (multiple statements)

WHEN MATCHED THEN
  UPDATE SET....

WHEN NOT MATCHED by target THEN
  INSERT (...)
     VALUES (...)
output $action into @Mergeoutput;
select * from @Mergeoutput

The update works if there's a match but the insert doesn't happen when there's no match. @Mergeout is empty. My update and insert statements should take action on one row. How does Merge work when the using statement is an empty set and I want to insert a new row?

Update:
This is the SQL to run everything. I expected @Mergeoutput to return 'Insert' and the Person table to have a new row.

Create table Person
(
  name varchar(20)
)

declare @Mergeoutput table (action varchar(50))

declare @newName varchar(20)

select @newName  = 'John'

MERGE Person AS target
using (Select name from Person where name= 'John')
    as Source (name)
on source.name = target.name

WHEN MATCHED THEN
  UPDATE SET name = 'John2'

WHEN NOT MATCHED THEN
  INSERT (name)
     VALUES ('John')
output $action into @Mergeoutput;
select * from @Mergeoutput

select * from person

Upvotes: 4

Views: 11747

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

Usually, your MERGE statement would look like this:

MERGE INTO @Person AS target
USING
(
    SELECT name 
    FROM @Person 
    WHERE <.......>
) AS Source
ON source.name = target.name

WHEN MATCHED THEN
UPDATE SET name = Source.name

WHEN NOT MATCHED THEN
INSERT (name)
VALUES (Source.name)
;

Note, that I take values from Source and insert them into Target.

WHEN NOT MATCHED [ BY TARGET ] THEN

Specifies that a row is inserted into target_table for every row returned by <table_source> ON <merge_search_condition> that does not match a row in target_table, but does satisfy an additional search condition, if present. The values to insert are specified by the <merge_not_matched> clause.

So, if your Source is empty, nothing will be inserted, regardless of the ON criteria.

It is not really clear what overall logic you are trying to achieve, but it seems that you may need to have some extra logic in the Source query.

Upvotes: 2

Related Questions