Update/Insert syntax vs Merge Into syntax

Mrao

I  have two
scenarios.  1. I have a source table containing only updates and I need to update a target table with the source table.  2.  I have a source table containing updates and inserts.  And I need to update/insert the target table with the source table.  What is the best way of updating the target table?

 

I believe I can use the below methods.

1.       Use Update syntax to update the target table with the source table.  Use the insert syntax to insert new records. 

2.       Use delete and insert approach.  Delete the records to be updated and insert all the records from the source table.

3.       Use merge into syntax for upsert.

Which one do you recommend?

In terms of performance, is there a difference between the 2nd and 3rd method?  Or it is just that the syntax is different but their performance is the same?

Samchu_Li

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

norbert_krupa

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.