SQL to Dynamics 365 Data Import - Upsert

  • 19Views
  • Last Post 23 April 2018
0
votes
Ivan Willis posted this 20 April 2018

Hi -

I was 'playing' around SQL to Dynamics 365 UPSERT functionality and there may be a bug in your SQL to Dynamics 365 upsert module. Upsert is supposed to update an existing record or insert if the key does not exist in the target environment. Anyway here is the error provided by the system.

contact With Id = 99206793-a0f9-4e70-ad19-64d084d6de57 Does Not Exist;"99206793-a0f9-4e70-ad19-64d084d6de57";"";""

contact With Id = 3611dfae-ff23-4757-9c2d-d0bbd185fe55 Does Not Exist;"3611dfae-ff23-4757-9c2d-d0bbd185fe55";"";""

 

Thanks!

0
votes
Mariia Zaharova posted this 23 April 2018

The UPSERT operation updates a record if it exists or inserts a new record. This allows you to avoid inserting duplicate data. You need to map the target ID/Primary key columns for performing UPSERT.

In Skyvia, UPSERT determines what action to perform in the following way: if a Null value is specified for the ID or primary key, UPSERT operation inserts the record, and if a non-null value is specified, UPSERT operation tries to update the record with the specified ID or primary key. Skyvia does not actually checks if such record exists, and providing invalid ID/PK values results in failed records.

 

You are using this mapping for ContactId field:

"target": "contactid",

"source": {"type": 1,"column": "crmId"}

 

In case when you map source SQL Server crmId column to destination Dynamics CRM ContactID field using column mapping:

- if crmId column is not null the update operation will be performed. Skyvia performs the update operation with the WHERE clause with this crmId value. If there is no such ContactId in target database table, this update will fail.

- if crmId column is null the insert operation will be performed. Skyvia performs the insert operation, new ContactId value will be generated at Dynamics CRM side. ContactId field cannot be assigned manually, this field is autogenerated.

 

We recommend you to use lookup mapping with the "Set null when no match found" option for the Upsert operation. To use the lookup mapping, you need to have a column (or a set of columns) that uniquely identifies a record, other than the primary key. 

 

Lookup mapping may be following:

 

Some useful information

https://skyvia.com/resources/docs/index.html?performing_upsert_operation.htm

https://skyvia.com/resources/docs/index.html?lookup_mapping.htm

 

If you encounter any issues with this or have any further questions, feel free to contact us. We will be glad to answer all your further questions.

 

Close