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)

begin

   update table set ...

   where key = @key

finish

else

begin

   place table (key, ...)

   values (@key, ...)

finish

commit tran

or

begin tran

   update table with (serializable) set ...

   where kay = @key

   if @@rowcount =

   begin

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

   finish

commit tran

Do an UPSERT:


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

IF @@ROWCOUNT =

   Place INTO MyTable (FieldA) VALUES (@FieldA)

http://en.wikipedia.org/wiki/Upsert

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

    update

    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 . . .

ELSE

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

Edit:

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