>>>Hi,
>>>
>>>How do you INSERT (possibly) and UPDATE a table in one command? Here is what I am trying to do:
>>>
>>>I need set fields, say MyField1 and MyField2, to some values MyVal1 and MyVal2 in a table where field MyField3 is equal to ‘MyVal3’ But if the record where MyField3 = ‘MyVal3’ does not exist, I want to INSERT a record and set values of all three fields.
>>>
>>>TIA
>>
>>In SQL Server 2008 and up you need to use MERGE command. Start from looking at MERGE command examples in BOL.
>>
>>Otherwise the basic idea is to use IF NOT EXISTS (..) INSERT ELSE UPDATE pattern which may fail in multi-user environment.
>
>FYI.
>
>Last year I wrote a application to populate a set of tables from various sources. It appeared to be a great opertunity to use the MERGE command. Well, everything went fine. But during some testing, I noticed that the after about four or five times the Merge command was executed, it stop updating existing rows and start just inserting duplicate records. I had to resort to the IF ELSE methed you are suggesting. BTW, this was in SQL 2008 R2.
Have you been using SP1? I think I read about some bug in MERGE command, try searching on MS Connect, I believe it should be there.
If it's not broken, fix it until it is.
My Blog