Update/Insert syntax vs Merge Into syntax

Hi Mrao,

Hopes this will help you.

for the method you listed, the best choice will be DELETE+INSERT, then UPDATE, and MERGE will be the worst.

The reason is:
1. DELETE + INSERT will do the following simple operations:
a. DELETE all the records with your WHERE condition
b. SELECT all the records you want from the source
c. INSERT into the target table

2. UPDATE
a.DELETE all the records with your WHERE condition
b.REBUILD all the records with your updated column and the unchanged columns
c.INSERT into the target table

which in UPDATE, the REBUILD step will cost higher.

3. MERGE INTO
MERGE actually need compare all the records between source and target, which will have a Cartesian space, then matched doing the UPDATE, matched doing the INSERT.

So compare UPDATE, we have one more high cost step.

Best Regards,
Samchu Li

If I understood your requirements correctly, you should be able to use a correlated UPDATE, then use TRUNCATE on your source table containing the updates. A correlated JOIN is faster than MERGE in this scenario. This will be the fastest/least resource intensive approach.