Insert Or Merge Entity

Article
06/27/2023

The Insert Or Merge Entity operation updates an existing entity, or inserts a new entity if it doesn't exist in the table. Because this operation can insert or update an entity, it's also known as an upsert operation.

Request

You can construct the Insert Or Merge Entity request as follows. HTTPS is recommended. Replace the following values with your own:

myaccount with the name of your storage account
mytable with the name of your table
myPartitionKey and myRowKey with the name of the partition key and row key for the entity to be updated

Method	Request URI	HTTP version
`MERGE`	`https://myaccount.table.core.windows.net/mytable(PartitionKey='myPartitionKey', RowKey='myRowKey')`	HTTP/1.1

Emulated storage service

When you make a request against the emulated storage service, specify the emulator hostname and Azure Table Storage port as 127.0.0.1:10002, followed by the emulated storage account name.

Method	Request URI	HTTP version
`MERGE`	`http://127.0.0.1:10002/devstoreaccount1/mytable(PartitionKey='myPartitionKey', RowKey='myRowKey')`	HTTP/1.1

Table Storage in the Storage emulator differs from the Azure Table Storage in several ways. For more information, see Differences between the Storage emulator and Azure Storage services.

URI parameters

You can specify the following additional parameter on the request URI.

Parameter	Description
`timeout`	Optional. The `timeout` parameter is expressed in seconds. For more information, see Setting timeouts for Table Storage operations.

Request headers

The following table describes required and optional request headers.

Request header	Description
`Authorization`	Required. Specifies the authorization scheme, account name, and signature. For more information, see Authorize requests to Azure Storage.
`Date` or `x-ms-date`	Required. Specifies the Coordinated Universal Time (UTC) for the request. For more information, see Authorize requests to Azure Storage.
`x-ms-version`	Required. Must be set to 2011-08-18 or later. Specifies the version of the operation to use for this request. For more information, see Versioning for the Azure Storage services.
`Content-Type`	Required. Specifies the content type of the payload. Possible values are `application/atom+xml` and `application/json`. For more formation about valid content types, see Payload format for Table Storage operations.
`Content-Length`	Required. The length of the request body.
`x-ms-client-request-id`	Optional. Provides a client-generated, opaque value with a 1-kibibyte (KiB) character limit that's recorded in the logs when logging is configured. We highly recommend that you use this header to correlate client-side activities with requests that the server receives. For more information, see Monitor Azure Table Storage.

Request body

The Insert Or Merge Entity operation sends the entity to be inserted as an OData entity set. This entity set can be either an Atom or JSON payload. For more information, see Inserting and updating entities.

Note

JSON is the recommended payload format, and is the only format supported for version 2015-12-11 and later.

Response

The response includes an HTTP status code and a set of response headers.

Status code

A successful operation returns status code 204 (No Content). For information about status codes, see Status and error codes, and Table Storage error codes.

Response headers

The response includes the following headers. The response can also include additional, standard HTTP headers. All standard headers conform to the HTTP/1.1 protocol specification.

Response header	Description
`ETag`	The `ETag` for the entity.
`x-ms-request-id`	Uniquely identifies the request that was made and can be used for troubleshooting the request. For more information, see Troubleshooting API operations.
`x-ms-version`	Indicates the version of Table Storage used to run the request. This header is returned for requests made against version 2009-09-19 and later.
`Date`	A UTC date/time value that indicates the time at which the response was initiated. The service generates this value.
`x-ms-client-request-id`	Can be used to troubleshoot requests and corresponding responses. The value of this header is equal to the value of the `x-ms-client-request-id` header, if it's present in the request. The value is at most 1,024 visible ASCII characters. If the `x-ms-client-request-id` header isn't present in the request, it won't be present in the response.

Response body

None.

Authorization

The account owner can perform this operation. Additionally, anyone with a shared access signature that has permission to perform this operation can do so.

Sample request and response

The following examples show sample requests that use JSON and Atom feeds.

Note

JSON is the recommended payload format, and is the only format supported for version 2015-12-11 and later.

JSON (version 2013-08-15 and later)

The following is a sample request and response that uses JSON.

MERGE https://myaccount.table.core.windows.net/mytable(PartitionKey='myPartitionKey',RowKey='myRowKey')

The request is sent with the following headers:

x-ms-version: 2013-08-15  
Content-Type: application/json  
x-ms-date: Tue, 30 Aug 2013 18:10:24 GMT  
Authorization: SharedKeyLite myaccount:u0sWZKmjBD1B7LY/CwXWCnHdqK4B1P4z8hKy9SVW49o=  
Content-Length: 1135  
DataServiceVersion: 3.0;NetFx  
MaxDataServiceVersion: 3.0;NetFx

The request is sent with the following JSON body:

{  
   "Address":"Santa Clara",  
   "Age":23,  
   "AmountDue":200.23,  
   "CustomerCode@odata.type":"Edm.Guid",  
   "CustomerCode":"c9da6455-213d-42c9-9a79-3e9149a57833",  
   "CustomerSince@odata.type":"Edm.DateTime",  
   "CustomerSince":"2008-07-10T00:00:00",  
   "IsActive":false,  
   "NumberOfOrders@odata.type":"Edm.Int64",  
   "NumberOfOrders":"255",  
   "PartitionKey":"mypartitionkey",  
   "RowKey":"myrowkey"  
}

After the request has been sent, the following response is returned:

  
HTTP/1.1 204 No Content  
  
Connection: Keep-Alive  
x-ms-request-id: 2c085f8f-11a4-4e1d-bd49-82c6bd87649d  
Content-Length: 0  
Cache-Control: no-cache  
Date: Tue, 30 Aug 2013 18:12:54 GMT  
ETag: W/"0x5B168C7B6E589D2"  
DataServiceVersion: 3.0;NetFx  
MaxDataServiceVersion: 3.0;NetFx  
Server: Windows-Azure-Table/1.0 Microsoft-HTTPAPI/2.0

Atom feed (versions prior to 2015-12-11)

The following is a sample request and response that uses Atom:

MERGE https://myaccount.table.core.windows.net/mytable(PartitionKey='myPartitionKey',RowKey='myRowKey')

The request is sent with the following headers:

x-ms-version: 2013-08-15  
Accept: application/atom+xml,application/xml  
Accept-Charset: UTF-8  
Content-Type: application/atom+xml  
x-ms-date: Tue, 12 Nov 2013 18:10:24 GMT  
Authorization: SharedKeyLite myaccount:u0sWZKmjBD1B7LY/CwXWCnHdqK4B1P4z8hKy9SVW49o=  
Content-Length: 1135  
DataServiceVersion: 1.0;NetFx  
MaxDataServiceVersion: 2.0;NetFx

The request is sent with the following XML body:

<?xml version="1.0" encoding="utf-8"?>  
<entry xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="https://www.w3.org/2005/Atom">  
  <title />  
  <updated>2013-11-12T18:09:37.168836Z</updated>  
  <author>  
    <name />  
  </author>  
<id>https://myaccount.table.core.windows.net/mytable(PartitionKey='mypartitionkey',RowKey='myrowkey')</id>  
  <content type="application/xml">  
    <m:properties>  
      <d:Address>Santa Clara</d:Address>  
      <d:Age m:type="Edm.Int32">23</d:Age>  
      <d:AmountDue m:type="Edm.Double">200.23</d:AmountDue>  
      <d:CustomerCode m:type="Edm.Guid">c9da6455-213d-42c9-9a79-3e9149a57833</d:CustomerCode>  
      <d:CustomerSince m:type="Edm.DateTime">2008-07-10T00:00:00Z</d:CustomerSince>  
      <d:IsActive m:type="Edm.Boolean">false</d:IsActive>  
      <d:NumOfOrders m:type="Edm.Int64">255</d:NumOfOrders>  
      <d:PartitionKey>mypartitionkey</d:PartitionKey>  
      <d:RowKey>myrowkey1</d:RowKey>  
    </m:properties>  
  </content>  
</entry>

After the request has been sent, the following response is returned:

HTTP/1.1 204 No Content  
  
Connection: Keep-Alive  
x-ms-request-id: 2c085f8f-11a4-4e1d-bd49-82c6bd87649d  
Content-Length: 0  
Cache-Control: no-cache  
Date: Tue, 12 Nov 2013 18:12:54 GMT  
ETag: W/"0x5B168C7B6E589D2"  
DataServiceVersion: 1.0;NetFx  
MaxDataServiceVersion: 2.0;NetFx  
Server: Windows-Azure-Table/1.0 Microsoft-HTTPAPI/2.0

Remarks

The Insert Or Merge Entity operation uses the MERGE verb. You must call the operation by using the 2011-08-18 version or later. In addition, this operation doesn't use the If-Match header. These attributes distinguish this operation from the Update Entity operation, though the request body is the same for both operations.

If you use the Insert Or Merge Entity operation to merge an entity, any properties from the previous entity are retained, if the request doesn't define or include them. Properties with a null value are also retained.

When you call the Insert or Merge Entity operation, you must specify values for the PartitionKey and RowKey system properties. Together, these properties form the primary key, and must be unique within the table.

Both the PartitionKey and RowKey values must be string values. Each key value can be up to 64 KiB in size. If you're using an integer value for the key value, you should convert the integer to a fixed-width string. This is because they are canonically sorted. For example, convert the value 1 to 0000001 to ensure proper sorting.

To explicitly type a property, specify the appropriate OData type by setting the m:type attribute within the property definition in the Atom feed. For more information about typing properties, see Inserting and updating entities.

Any application that can authorize and send an HTTP MERGE request can insert or update an entity.

For information about performing batch upsert operations, see Performing entity group transactions.