Use Upsert to insert or update a record

You can reduce the complexity involved with data integration scenarios by using the UpsertRequest message. When loading data into Microsoft Dataverse from an external system, for example in a bulk data integration scenario, you may not know if a record already exists in Dataverse. In such cases you won’t know if you should call an UpdateRequest or a CreateRequest operation. This results in your querying for the record first to determine if it exists before performing the appropriate operation. You can now reduce this complexity and load data into Dataverse more efficiently by using the new UpsertRequest (Update or Insert) message.

Using Upsert

It is best to use UpsertRequest only when you aren’t sure if the record exists. That is, when you aren’t sure if you should call a CreateRequest or an UpdateRequest operation. There is a performance decrease in using UpsertRequest versus using CreateRequest. If you are sure the record doesn’t exist, use CreateRequest.

The UpsertRequest includes a property named Target. This property contains the table definition that will be used in an UpdateRequest or a CreateRequest operation. It also includes all the columns required by the CreateRequest for the target table type so that the record can be created if it doesn’t exist.

You can inspect RecordCreated to determine if the record was created. RecordCreated will be true if the record didn’t exist and was created. It will be false if the record already existed and was updated. Target will be an EntityReference to the record that was found to exist or to the record that was created.

To understand how UpsertRequest works, see the following section.

Understanding the Upsert process

The following steps describe the processing logic when an UpsertRequest is received:

1. Send UpsertRequest with enough data for a create or insert operation.

2. Dataverse will look up the record targeted by the target table.

3. If the record exists:

   1. Set the ID property of the target table with the ID of the found record.

   2. Call Update.

   3. Set the RecordCreated to false.

   4. Create an EntityReference from the target table of the update as the value for Target.

   5. Return the UpsertResponse.

4. If the record doesn’t exist:

   1. Copy any alternate key values into the target table columns.

   2. Call Create.

   3. Set the RecordCreated to true.

   4. Create an EntityReference from the target table type and the ID result of the Create request as the value for Target.

   5. Return the UpsertResponse.

   The following illustration shows the process that unfolds when an UpsertRequest is received.

   

Sample code

The Insert or update a record using Upsert sample ProductUpsertSample.cs file contains the following ProcessUpsert method to apply the UpsertRequest message on the contents of an XML file to create new records or update existing ones.

public void ProcessUpsert(String Filename)
{
    Console.WriteLine("Executing upsert operation.....");
    XmlTextReader tr = new XmlTextReader(Filename);
    XmlDocument xdoc = new XmlDocument();
    xdoc.Load(tr);
    XmlNodeList xnlNodes = xdoc.DocumentElement.SelectNodes("/products/product");

    foreach (XmlNode xndNode in xnlNodes)
    {
        String productCode = xndNode.SelectSingleNode("Code").InnerText;
        String productName = xndNode.SelectSingleNode("Name").InnerText;
        String productCategory = xndNode.SelectSingleNode("Category").InnerText;
        String productMake = xndNode.SelectSingleNode("Make").InnerText;

        //use alternate key for product
        Entity productToCreate = new Entity("sample_product", "sample_productcode", productCode);

        productToCreate["sample_name"] = productName;
        productToCreate["sample_category"] = productCategory;
        productToCreate["sample_make"] = productMake;
        UpsertRequest request = new UpsertRequest()
        {
            Target = productToCreate
        };

        try
        {
            // Execute UpsertRequest and obtain UpsertResponse. 
            UpsertResponse response = (UpsertResponse)_serviceProxy.Execute(request);
            if (response.RecordCreated)
                Console.WriteLine("New record {0} is created!", productName);
            else
                Console.WriteLine("Existing record {0} is updated!", productName);
        }

        // Catch any service fault exceptions that Microsoft Dynamics CRM throws.
        catch (FaultException<Microsoft.Xrm.Sdk.OrganizationServiceFault>)
        {
            throw;
        }

    }
    // Prompts to view the sample_product entity records.
    // If you choose "y", IE will be launched to display the new or updated records.
    if (PromptForView())
    {
        ViewEntityListInBrowser();
    }

}

See also

Use change tracking to synchronize data with external systems
Define alternate keys for a table
Using alternate keys