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

Frequently I wish to either update a current record, or place a brand new record whether it does not exist.

basically if (key is available) Run Update command ELSE run place command

What is the best carrying out method to write this?

Men, remember about transactions. Performance is nice, but simple (IF Is available..) approach is extremely harmful.
When multiple threads will attempt to do Place-or-update it is simple to get primary key breach.

Solutions supplied by @Love Crawford &lifier @Esteban show general idea but error-prone.

To prevent deadlocks and PK violations you should use something similar to this:

begin tran

if is available (choose * from table with (updlock,serializable) where key = @key)


   update table set ...

   where key = @key




   place table (key, ...)

   values (@key, ...)


commit tran


begin tran

   update table with (serializable) set ...

   where kay = @key

   if @@rowcount =


  place table (key, ...) values (@key,..)


commit tran


UPDATE MyTable SET FieldA=@FieldA WHERE Key=@Key


   Place INTO MyTable (FieldA) VALUES (@FieldA)

See my detailed response to a really similar previous question

@Love Crawford's is a great means by SQL 2005 and below, though if you are granting repetition it will visit the first guy to Therefore it. The only issue is the fact that for card inserts will still be two IO procedures.

MS Sql2008 introduces merge in the SQL:2003 standard:

merge tablename as target

using (values ('new value', 'different value'))

    as source (field1, field2)

    on target.idfield = 7

when matched up then


    set field1 = source.field1,

        field2 = source.field2,


if not matched up then

    place ( idfield, field1, field2, ... )

    values ( 7,  source.field1, source.field2, ... )

Now it is only one IO operation, but awful code :-(

If you wish to UPSERT several record at any given time you should use the ANSI SQL:2003 DML statement MERGE.

MERGE INTO table_title WITH (HOLDLOCK) USING table_title ON (condition)

WHEN Matched up THEN UPDATE SET column1 = value1 [, column2 = value2 ...]

If Not Matched up THEN Place (column1 [, column2 ...]) VALUES (value1 [, value2 ...])

Take a look at Resembling MERGE Statement in SQL Server 2005.

IF Is available (Choose * FROM [Table] WHERE ID = rowID)

UPDATE [Table] SET propertyOne = propOne, property2 . . .


Place INTO [Table] (propOne, propTwo . . .)


Alas, even going to my very own hindrance, I have to admit the solutions that this w/o a choose appear to become better given that they get the job done with one less step.

In SQL 2008 you should use the MERGE statement