How to Efficiently Perform Upserts in SQL Server?

Efficiently perform insert or update operations in SQL Server

When dealing with CRUD operations, you often encounter situations where you need to update existing records or insert new records when the primary key does not exist. This is equivalent to "IF (primary key exists) UPDATE else INSERT" logic.

The question is: how to achieve this with optimal performance?

SQL Server Solution

SQL Server provides several solutions:

1. Use the MERGE statement

The

MERGE statement provides a concise way to implement this logic. It allows you to specify an update operation to be performed if the primary key exists, and an insert operation to be performed if the primary key does not exist.

<code class="language-sql">MERGE INTO MyTable WITH (HOLDLOCK) AS Target
USING (VALUES (@key, @datafield1, @datafield2)) AS Source(KEY, datafield1, datafield2)
ON (Target.KEY = Source.KEY)
WHEN MATCHED THEN
    UPDATE SET datafield1 = Source.datafield1, datafield2 = Source.datafield2
WHEN NOT MATCHED THEN
    INSERT (KEY, datafield1, datafield2)
    VALUES (Source.KEY, Source.datafield1, Source.datafield2);</code>

2. Use transactions

Another approach is to use transactions to ensure data integrity and prevent primary key conflicts.

<code class="language-sql">BEGIN TRANSACTION;
IF EXISTS (SELECT * FROM MyTable WITH (XLOCK) WHERE KEY = @key)
BEGIN
    UPDATE MyTable SET datafield1 = @datafield1, datafield2 = @datafield2
    WHERE KEY = @key;
END
ELSE
BEGIN
    INSERT INTO MyTable (KEY, datafield1, datafield2)
    VALUES (@key, @datafield1, @datafield2);
END
COMMIT TRANSACTION;</code>

3. Use the OUTPUT clause in conjunction with UPDATE and INSERT

A third option is to use an OUTPUT clause to capture the number of rows affected by the UPDATE operation. This information can then be used to determine whether an INSERT operation is required.

<code class="language-sql">BEGIN TRANSACTION;
UPDATE MyTable SET datafield1 = @datafield1, datafield2 = @datafield2
OUTPUT inserted.KEY INTO @AffectedRows
WHERE KEY = @key;

IF @AffectedRows = 0
BEGIN
    INSERT INTO MyTable (KEY, datafield1, datafield2)
    VALUES (@key, @datafield1, @datafield2);
END
COMMIT TRANSACTION;</code>

The above is the detailed content of How to Efficiently Perform Upserts in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!