1

I am looking at the MERGE statement, and I do not understand the point of it.

Correct me if I am wrong, but basically, it makes the target table an identical copy of the source table.

So isn't it easier to just DELETE records from 1 table and simply INSERT INTO ... SELECT all rows from another?

Is there something I am missing in the efficiency of the MERGE?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Lana B
  • 496
  • 6
  • 17
  • someone has voted to close my ticket without as much as saying a word in a comment. Maybe from the height of your expertise this is not interesting, but i think it is a question as valid as any other: i want to understand the difference in using these two methods to update a table. What is wrong with that? at least say something so i know what I do wrong here? thanks – Lana B Jan 29 '20 at 13:28
  • 1
    Hi Lana, someone voted to close your question (it's not a ticket) as 'primarily opinion-based'. We don't know who (close votes are anonymous until the question is ultimately closed), but we can guess at why: you're asking "isn't it easier to do X", but "easier" is not an objectively measurement. What's easier for you may not be easier for me, for example. Stack Overflow questions should be formed in such a way that they can be *objectively* answerable. Instead of "easier", maybe ask about shorter code, shorter runtime to query completion, avoiding some loss of data, etc. Hope that helps! – TylerH Jan 29 '20 at 14:50
  • thanks now I understood, will do my best to formulate questions better in the future! :) – Lana B Jan 29 '20 at 18:46

1 Answers1

6

A Merge statement does not make the target table a copy of the source table, You've got that part wrong.

It provides a shorter way to write conditional DML statements - insert / update / delete - by letting you write a single statement that will execute either one (or any combination of) the above DML statements.

However, even though Merge is written as a single statement, behind the sceens SQL Server actually execute it as a series of statements - each part of it gets it's one statement - and this can cause plenty of problems for the unexpecting user.
Aaron bertrand have published an article about this entitled Use Caution with SQL Server's MERGE Statement - you should read it thoroughly before using the Merge statement in SQL Server.

Most of the time, Merge is used to perform an "upsert" - meaning - update the record(s) if exists, or insert if not. A better, safer pattern for "upsert" in SQL server is described in Aaron's answer to this SO question, And I highely recommend using that over Merge any time it's possible.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • okay if merge does not have to do all insert+update+delete, then the efficiency is in syntax. it's clearly more concise than writing a subquery and join to find rows that are needed. Thank you for your answer, I'll go read the article too. Muchas gracias. – Lana B Jan 29 '20 at 14:16