Solutions for INSERT OR UPDATE on SQL Server

Assume a table structure of `MyTable(KEY, datafield1, datafield2...)`.

Often I want to either update an existing record, or insert a new record if it doesn't exist.

Essentially:

IF (key exists)
run update command
ELSE
run insert command

What's the best performing way to write this?

Do a select, if you get a result, update it, if not, create it.

Do an UPSERT:

<pre>
UPDATE MyTable SET FieldA=@FieldA WHERE Key=@Key

IF @@ROWCOUNT = 0
INSERT INTO MyTable (FieldA) VALUES (@FieldA)
</pre>

[To see links please register here]

Doing an if exists ... else ... involves doing two requests minimum (one to check, one to take action). The following approach requires only one where the record exists, two if an insert is required:

DECLARE @RowExists bit
SET @RowExists = 0
UPDATE MyTable SET DataField1 = 'xxx', @RowExists = 1 WHERE Key = 123
IF @RowExists = 0
INSERT INTO MyTable (Key, DataField1) VALUES (123, 'xxx')

I usually do what several of the other posters have said with regard to checking for it existing first and then doing whatever the correct path is. One thing you should remember when doing this is that the execution plan cached by sql could be nonoptimal for one path or the other. I believe the best way to do this is to call two different stored procedures.

<pre>
FirstSP:
If Exists
Call SecondSP (UpdateProc)
Else
Call ThirdSP (InsertProc)
</pre>

Now, I don't follow my own advice very often, so take it with a grain of salt.

If you use ADO.NET, the DataAdapter handles this.

If you want to handle it yourself, this is the way:

Make sure there is a primary key constraint on your key column.

Then you:

1. Do the update
2. If the update fails because a record with the key already exists, do the insert. If the update does not fail, you are finished.

You can also do it the other way round, i.e. do the insert first, and do the update if the insert fails. Normally the first way is better, because updates are done more often than inserts.

MS SQL Server 2008 introduces the MERGE statement, which I believe is part of the SQL:2003 standard. As many have shown it is not a big deal to handle one row cases, but when dealing with large datasets, one needs a cursor, with all the performance problems that come along. The MERGE statement will be much welcomed addition when dealing with large datasets.

Although its pretty late to comment on this I want to add a more complete example using MERGE.

Such Insert+Update statements are usually called "Upsert" statements and can be implemented using MERGE in SQL Server.

A very good example is given here:

[To see links please register here]

The above explains locking and concurrency scenarios as well.

I will be quoting the same for reference:

ALTER PROCEDURE dbo.Merge_Foo2
@ID int
AS

SET NOCOUNT, XACT_ABORT ON;

MERGE dbo.Foo2 WITH (HOLDLOCK) AS f
USING (SELECT @ID AS ID) AS new_foo
ON f.ID = new_foo.ID
WHEN MATCHED THEN
UPDATE
SET f.UpdateSpid = @@SPID,
UpdateTime = SYSDATETIME()
WHEN NOT MATCHED THEN
INSERT
(
ID,
InsertSpid,
InsertTime
)
VALUES
(
new_foo.ID,
@@SPID,
SYSDATETIME()
);

RETURN @@ERROR;

Before everyone jumps to HOLDLOCK-s out of fear from these nafarious users running your sprocs directly :-) let me point out that **you have to guarantee uniqueness of new PK-s by design** (identity keys, sequence generators in Oracle, unique indexes for external ID-s, queries covered by indexes). That's the alpha and omega of the issue. If you don't have that, no HOLDLOCK-s of the universe are going to save you and if you do have that then you don't need anything beyond UPDLOCK on the first select (or to use update first).

Sprocs normally run under very controlled conditions and with the assumption of a trusted caller (mid tier). Meaning that if a simple upsert pattern (update+insert or merge) ever sees duplicate PK that means a bug in your mid-tier or table design and it's good that SQL will yell a fault in such case and reject the record. Placing a HOLDLOCK in this case equals eating exceptions and taking in potentially faulty data, besides reducing your perf.

Having said that, Using MERGE, or UPDATE then INSERT is easier on your server and less error prone since you don't have to remember to add (UPDLOCK) to first select. Also, if you are doing inserts/updates in small batches you need to know your data in order to decide whether a transaction is appropriate or not. It it's just a collection of unrelated records then additional "enveloping" transaction will be detrimental.

If you want to UPSERT more than one record at a time you can use the ANSI SQL:2003 DML statement MERGE.

MERGE INTO table_name WITH (HOLDLOCK) USING table_name ON (condition)
WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])

Check out [Mimicking MERGE Statement in SQL Server 2005](

[To see links please register here]

).