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

Thanks.

P.S. The version of my Query tool will allow you to use the Merge command against many data sourcces, including Foxpro tables.