CODEWITHSUNDEEP
sql-server
Anthony Elliott
Anthony Elliott

Reputation: 3001

Why does sql-server prevent inserting in WHEN MATCHED of merge?

Anyone know why sql server prevents inserting from within the WHEN MATCHED clause of a MERGE statement? I understand that the documentation only allows updates or deletes, I'm wondering why this is the case so I can understand merge better.

Look at this post for an example.

Upvotes: 5

Views: 3509

Answers (4)

Java
Java

Reputation: 1

I think the case might be if I want to keep track of history. e.g. Telephone field; I want to see what was the telephone before you change it.

Upvotes: 0

user3062285
user3062285

Reputation: 1

Common sense says: if you already have it there (the record) why would you want to insert it again? Not to mention that normally the "matching" is on a non duplicated key. If you are able to find a situation where a matched record needs to be inserted again, let us know to help you.

Upvotes: 0

Der U
Der U

Reputation: 3314

As you want to INSERT when you find a MATCH, i presume the condition of the ON-clause is met but another field is different. Consider including this field into the ON-clause with AND to differentiate between present rows and to be inserted rows.

Upvotes: 1

ApplePie
ApplePie

Reputation: 8942

If you are trying to merge your source to your target, it does not make sense to insert a line if it was found in the target. You may want to update or delete it though. Inserting what is already there would create duplicates.

Upvotes: 2

Related Questions