Need advice on IF...THEN...ELSE type T-SQL syntax

I don't write SQL that often, so...

What is the most "efficient" way to write an SQL statement that accomplishes:

If a record for this key exists in this table, just update it with the following information, but if a record does not exist, create one with the information.

Thanks in advance.

Look into If Exists.

|||

try this

ifexists(SELECT key_fieldfrom yourtablewhere key_field='key')BEGINUPDATE yourtableset [thefield]='your new value'WHERE key_field='key'END

|||

My understanding is that with the 2008 release of sql server, it will add the MERGE command, which is designed precisely for this purpose.

Otherwise, you'll have to use the prior example (but remember to add an "else" branch to do the insert.

Alternatively, there is a way to do it as two statements (one update, one insert) that will handle all the rows you need updated/inserted.

It just depends upon the nature of the update. If all the records to be updated need to be updated with the same info (i.e., add 5% to the salary), one update statement will update all of them.

Then, do an insert statement in the form of:

insert into destination_table (col1,col2) select colA, colB from source_table where source_table.key_column not in (select key_column from destination_table)

That will insert all the missing records.

|||

Thanks for the push, all. I also came upon the slightly different approach of

Do the Update, if the @.@.ROWCOUNT equals 0, do the insert.

|||

DisturbedBuddha:

Thanks for the push, all. I also came upon the slightly different approach of

Do the Update, if the @.@.ROWCOUNT equals 0, do the insert.

Duh! Much easier and probably more efficient than the exists check.

|||

http://disturbedbuddha.wordpress.com/2007/11/29/easy-sql-if-record-exists-update-it-if-not-insert-it/