Dynamics Community Forum Thread Details

SQL to Dynamics 365

I have a few tables in SQL server which I would like to expose in Dynamics 365.

Here is an example of some tables.

-- Customer Table
CREATE TABLE dbo.Customers (
  ID INT IDENTITY(1,1) PRIMARY KEY,
  FirstName VARCHAR(10),
  LastName VARCHAR(10),
  DateOfBirth DATE
)

-- Customer Contact Number Table
CREATE TABLE dbo.ContactNumbers  (
  ID INT IDENTITY(1,1) PRIMARY KEY,
  CustomerID INT,
  NumberType VARCHAR(10),
  PhoneNumber VARCHAR(20)
 )

ALTER TABLE dbo.ContactNumbers    
ADD CONSTRAINT FK_ContactNumbers_CustomerID FOREIGN KEY (CustomerID)     
    REFERENCES dbo.Customers (ID)

-- Customer Address Table
CREATE TABLE dbo.CustomerAddress  (
  ID INT IDENTITY(1,1) PRIMARY KEY,
  CustomerID INT,
  AddressTypeType VARCHAR(10),
  AddressLine1 VARCHAR(50),
  AddressLine2 VARCHAR(50),
  AddressLine3 VARCHAR(50),
  AddressLine4 VARCHAR(50),
  Country VARCHAR(30)
 )

ALTER TABLE dbo.CustomerAddress   
ADD CONSTRAINT FK_CustomerAddress_CustomerID FOREIGN KEY (CustomerID)     
    REFERENCES dbo.Customers (ID)

Just please be aware these are just example tables and I have many more with different names and for different purposes. I am not interested in using the pre-made entities which come with Dynamics.

I have also created three tables in PowerApp which match the tables shown above.

My understanding is there is no easy way to bulk import all this data at once and it will automatically map it. Is this correct?

My understanding is that you can't "join" tables together using specific columns (CustomerID in this example), and that I have to import the Customers table first which will create an internal guid on each customer, then use this guid when passing over the other tables (ContactNumbers, CustomerAddress)? I would be interested to know if that correct method.

Quick Links