Import Accounts and Contacts into Dynamics CRM

How to Import Accounts and Contacts into Dynamics CRM


Step 1: Prepare the source SQL tables

For this example we have 2 tables 'Account' and 'Contact', these 2 tables are linked via a Foreign Key Contact.AccountID –> Account.ID.

Account SQL Table
Account

Contact SQL Table

Contact

Dynamics CRM doesn't work with Integer keys it uses Guids for the Primary Key. In many cases it may be impossible to change the source database to update each record with a Guids. We are using Data Synchronisation Studio which can manage the GUIDs without making any changes to your SQL Database or Dynamics CRM.

Step 2 - Import your Accounts from SQL

This is an easy Data Sync project where you simply import your accounts table into the Accounts entity mapping name to name and id to accountnumber.

Project in Data Sync

Project

Schema Map

Schema Map

We now just sync the changes into the CRM Account entity.

Step 3 - Import your Contacts

This step requires mapping the Contact table to the Contact entity in CRM mapping the ID value from the SQL Table to the ExternalUserIdentifier column in CRM.

Project in Data Sync

Project

SQL Schema Map

Schem Map

Step 4: Define a Lookup between AccountID and the accountnumber in CRM.

We need one more trick to automatically associate the Contact with the imported Account record in Dynamics CRM. For this we define a Lookup between AccountIDand the accountnumber in CRM.

Select the AccountID column in the Data source window and click the 'Add Lookup' button.

Add Lookup

Next configure the Lookup Data source to connect back to the Dynamics CRM account entity.

Connect

Next define the Lookup relationship by selecting accountnumber in the Target Column field.

Define Lookup

Now you can use all the columns from the related Account CRM entity in your project.

Lookup Columns

We now need to map Lookup1_accountid to parentcustomerid to create the relationship in Dynamics CRM.

Schema Map

Step 5: Preview and Synchronise the Contacts

Results


Can't find the answers you're looking for? Contact our support team and we'll personally get back to you as quickly as we can.