Edit

Use Upsert to Create or Update a record

  • Article
  • 4 minutes to read

You can reduce the complexity involved with data integration scenarios by using the Upsert 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 use the Update or a Create message. You must retrieve the record first to determine if it exists before performing the appropriate operation. You can reduce this complexity and load data into Dataverse more efficiently by using the Upsert message.

There is a performance penalty in using Upsert versus using Create. If you are sure the record doesn't exist, use Create.

Note

While you can use primary key values with Upsert, it is generally expected that you will be using alternate keys because the common use case is data integration scenarios. More information: Use an alternate key to reference a record

Understanding the Upsert process

Upsert messages are processed on the server. The SDK for .NET classes use the same objects used on the server. Therefore, the following explaination uses the SDK for .NET classes to describe how an UpsertRequest instance is processed what is returned by the UpsertResponse instance.

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

  1. The UpsertRequest instance arrives with the Target Property set with an Entity instance containing the data for a Create or Update operation.
  2. If it exists, Dataverse will try to look up the record using the Entity.Id property of the Entity instance set to the Target property. Otherwise it will use the alternate key values from the Entity.KeyAttributes Property.
  3. If the record exists:
    1. Set the Target Entity.Id with the primary key value of the found record.
    2. Remove any data from the Target Entity.Attributes collection that have use the same keys used in the Target Entity.KeyAttributes collection.
    3. Call Update.
    4. Set the UpsertResponse.RecordCreated property to false.
    5. Create an EntityReference from the Target entity as the value for UpsertResponse.Target.
    6. Return the UpsertResponse.
  4. If the record doesn't exist:
    1. Copy any data from the Target Entity.KeyAttributes that the Target does not already have in its Entity.Attributes collection into the Target Entity.Attributes.
    2. Call Create.
    3. Set the UpsertResponse.RecordCreated to true.
    4. Create an EntityReference from the Target entity and the id result of the Create operation as the value for UpsertResponse.Target.
    5. Return the UpsertResponse.

The following diagram shows the process on the server when an UpsertRequest is received.

upsert process flow

Guidance for composing requests

When using alternate keys to identify a record, including the alternate key data in the portion of the request that represents the data to be saved is not recommended or necessary.

If you are using the Web API and not familar with the SDK for .NET, the server-side process described above may be difficult to follow. The Web API doesn't have the same object model as the SDK objects used in the description and the diagram above, but the data can be mapped as shown in the table below.

Web API SDK Description
Key values in URL Entity.KeyAttributes Property Contains the alternate key data to identify the record.
Body of request The Entity set to the UpsertRequest.Target Property Contains the data to use for Create or Update.

Although these requests are processed on the server as described above, you can think of it this way:

  • If the record exists: The data set in the body of the request for those alternate key values in the Url will be removed, so there is no point in including it. This ensures that you cannot update the alternate key values of a record when you are using those alternate key values to identify it. You can change alternate key values using the primary key or a different set of alternate keys.
  • If the record doesn't exist: Any alternate key values set in the body of the request will be used to create the new record, even if the data is different than the values specified by the alternate keys in the Url. If there is no alternate key data in the body of the request, the alternate key data from the URL will be copied into the body of the request. To avoid a situation where the key values in the Url and the corresponding key values in the body don't match, it is best to not include them in the body at all.

Using Web API

With the Web API the Upsert and Update messages are both initiated using http PATCH against a specified EntitySet resource identified by the keys in the Url.

The difference between Upsert and Update is defined by whether the If-Match: * request header is included. If the If-Match: * request header is included and no resource can be identified by the key values in the Url, the request will return a 404 Not Found status code. The If-Match: * request header ensures that the PATCH request is an Update operation.

If the If-Match: * request header is not included, the PATCH request is treated like an Upsert and a new record will be created if no records matching the keys in the URL are found. However, unlike the SDK, the response will not tell you whether a record was created. The status response will be 204 No Content in either case.

If you include a Prefer: return=representation request header the system will return a 201 Created status for Create, and a 200 OK status for Update. This adds an additional Retrieve operation so it has an impact on performance. If you use this option, make sure that the $select query option you add only includes the primary key value. More information:

With a PATCH request you can also include the If-None-Match: * request header to block an Update if you only want to create records. More information: Limit upsert operations

Web API Sample code

The following examples show Upsert operations using a table with two alternate key columns:

Create with Upsert

This request creates a record.

Request

PATCH [Organization Uri]/api/data/v9.2/example_records(example_key1=2,example_key2=2) HTTP/1.1
OData-MaxVersion: 4.0
OData-Version: 4.0
If-None-Match: null
Accept: application/json
Content-Type: application/json

{ "example_name": "2:2" }

Response

HTTP/1.1 204 No Content
OData-Version: 4.0
OData-EntityId: [Organization Uri]/api/data/v9.2/example_records(example_key1=2,example_key2=2)

Update with Upsert

This request updates the record created by the request above.

Request

PATCH [Organization Uri]/api/data/v9.2/example_records(example_key1=2,example_key2=2) HTTP/1.1
OData-MaxVersion: 4.0
OData-Version: 4.0
If-None-Match: null
Accept: application/json
Content-Type: application/json

{ "example_name": "2:2 Updated" }

Response

HTTP/1.1 204 No Content
OData-Version: 4.0
OData-EntityId: [Organization Uri]/api/data/v9.2/example_records(example_key1=2,example_key2=2)

Note

The response is identical for Create or Update operations.

Create with Upsert and and return=representation preference

When you use the Prefer: return=representation header you can get a different status code in the response to indicate whether the record was created or updated.

The following request creates a new record and returns status 201 Created.

Request

PATCH [Organization Uri]/api/data/v9.2/example_records(example_key1=3,example_key2=3)?$select=example_recordid HTTP/1.1
OData-MaxVersion: 4.0
OData-Version: 4.0
If-None-Match: null
Accept: application/json
Prefer: return=representation
Content-Type: application/json

{ "example_name": "3:3" }

Response

HTTP/1.1 201 Created
Content-Type: application/json; odata.metadata=minimal
ETag: W/"71004878"
Preference-Applied: return=representation
OData-Version: 4.0

{
  "@odata.context": "[Organization Uri]/api/data/v9.2/$metadata#example_records(example_recordid)/$entity",
  "@odata.etag": "W/\"71004878\"",
  "example_recordid": "ef0d112e-d70e-ed11-82e5-00224822577b"
}

Update with Upsert and return=representation preference

This request updates the record created by the request above and returns status 200 OK to show that this was an update operation.

Request

PATCH [Organization Uri]/api/data/v9.2/example_records(example_key1=3,example_key2=3)?$select=example_recordid HTTP/1.1
OData-MaxVersion: 4.0
OData-Version: 4.0
If-None-Match: null
Accept: application/json
Prefer: return=representation
Content-Type: application/json

{ "example_name": "3:3 Updated" }

Response

HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal
ETag: W/"71004880"
OData-Version: 4.0

{
  "@odata.context": "[Organization Uri]/api/data/v9.2/$metadata#example_records(example_recordid)/$entity",
  "@odata.etag": "W/\"71004880\"",
  "example_recordid": "ef0d112e-d70e-ed11-82e5-00224822577b"
}

Using the SDK for .NET

Your client application will use the IOrganizationService.Execute Method with an UpsertRequest instance which has the Target Property set with an Entity instance containing the data for a Create or Update operation. The Entity instance will typically have the Entity.KeyAttributes Property set with values used to identify the record using alternate keys.

The UpsertResponse.RecordCreated Property tells you whether the record was created, and the UpsertResponse.Target contains a reference to the record that was created or updated.

SDK for .NET Sample code

The SampleMethod.cs file in the Insert record using Upsert sample 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 static void ProcessUpsert(CrmServiceClient service, 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;
        var request = new UpsertRequest()
        {
            Target = productToCreate
        };

        try
        {
            // Execute UpsertRequest and obtain UpsertResponse.
            var response = (UpsertResponse)service.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 Dataverse throws.
        catch (FaultException<Microsoft.Xrm.Sdk.OrganizationServiceFault>)
        {
            throw;
        }
    }
}

See also

Use change tracking to synchronize data with external systems
Define alternate keys for a table
Use an alternate key to reference a record