Import Accounts and Contacts into Dynamics CRM
How to Import Accounts and Contacts into Dynamics CRM
9 May 2012
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
Contact SQL Table
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 Synchronisation Studio
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.
SQL Schema 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.
Next configure the Lookup Data source to connect back to the Dynamics CRM account entity.
Next define the Lookup relationship by selecting accountnumber in the Target Column field.
Now you can use all the columns from the related Account CRM entity in your project.
We now need to map Lookup1_accountid to parentcustomerid to create the relationship in Dynamics CRM.
Step 5: Preview and Synchronise the Contacts
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.