Three ways to Upsert in SQL Server

This article describes three common ways to Upsert in SQL Server and their performance comparisons.

SQL Server does not support native Upsert statements, and typically uses composite statements to implement Upsert functionality.

Assuming there is a table table_a, the fields are as follows:

The int type ID is the primary key.

Method 1: Query first, use INSERT or update based on query results

IF EXISTS(SELECT 1  fromTable_aWHEREId= @Id)    BEGIN        UPDATEdbo.table_aSETValue= @Value        WHEREId= @Id; ENDELSE    BEGIN        INSERT  intodbo.table_a (Id, Value)VALUES(@Id,@Value)    END

Method 2: Update first to determine if you need to insert the number of entries affected by the update results

UPDATEdbo.table_aSETValue= @ValueWHEREId= @Id; IF(@ @ROWCOUNT = 0)BEGIN    INSERT  intodbo.table_a (Id, Value)VALUES(@Id,@Value)END

Method 3: Use the merge statement to Upsert data as source,merge to the target table

MERGE intoTable_a astusing (SELECT @Id  asId@Value  asValue) asS onT.id=s.id whenMatched Then    UPDATE SETT.value=S.value when  notMatched Then    INSERT(Id, Value)VALUES(S.id, S.value);

Performance comparison

Randomly upsert10 in 500,000 rows of data items

Scenario One: Upsert data item 100% hit update

Scenario Two: Upsert data entry 100% hit insert

Scenario Three: Upsert data item ID is random number, ~50%insert,~50%update

It can be seen that there is partial deviation of the experimental data, in general, the performance difference between the three methods is very small. For most programs where Upsert is not a critical path, Method 2 is a better solution for readability and performance.

In the case of performance-critical scenarios, you can choose the merge statement, the following are the advantages of the merge statement: "

1. Faster performance. The Engine needs to parse, compile, and execute only one query instead of three (and no temporary variable to the key ).
2. Neater and simpler T-SQL code (after you get the proficient in MERGE).
3. No need for explicit BEGIN transaction/commit. MERGE is a single statement and was executed in one implicit transaction.
4. Greater functionality. MERGE can delete rows that is not matched by source (SRC table above). For example, we can delete row 1 from a_table because it Data column does not match search_col in the SRC Table. There is also a-return inserted/deleted values using the OUTPUT clause. "

Reference: Http://www.sergeyv.com/blog/archive/2010/09/10/sql-server-upsert-equivalent.aspx

Three ways to Upsert in SQL Server