Prep Data for Transfer to Dataverse

[RonKochanowski]

We are moving a solution from on-prem SQL to Dataverse and need to first do some clean-up. A SQL table, core_person_phone, is defined as:

CREATE TABLE [dbo].[core_person_phone](
	[person_id] [int] NOT NULL,
	[phone_luid] [int] NOT NULL,
	[phone_number] [varchar](50) NOT NULL,
	[phone_ext] [varchar](50) NOT NULL,
	[unlisted] [bit] NOT NULL,
	[phone_number_stripped] [varchar](50) NOT NULL,
	[sms_enabled] [bit] NOT NULL,
	[sms_provider_luid] [int] NULL,
	[organization_id] [int] NOT NULL,
 CONSTRAINT [PK_core_person_phones] PRIMARY KEY CLUSTERED 
(
	[person_id] ASC,
	[phone_luid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

One of the [phone_luid] choices is 282, which is Mobile Phone. On the Dataverse side, a Mobile Phone is unique to the table, and is a stand alone field. Converting this entity will first require that I make the SQL values unique across the table, not only by [phone_luid].

So here's what I'm trying to find an answer to: How to make a Mobile Phone (phone_luid = 282) unique to that type, and not allowed in any other phone type. There can be one Mobile Phone defined in the table, period.

Any help would be greatly appreciated!

[#2]

@RonKochanowski , your post is not very clear and next time please post some sample data as DML statement and the expected result.

To get duplicate phone_number you can use this query:

SELECT phone_number, COUNT(*) AS Cnt
FROM dbo.core_person_phone
GROUP BY phone_number
HAVING COUNT(*) > 1

[#3]

@olafhelper, Thanks for the reply, but a query to lookup duplicates is not what I'm looking for. I need a Constraint definition to prevent duplicates from occurring.

[#4]

I need a Constraint definition to prevent duplicates from occurring.

---

@RonKochanowski , ????

A primary key is such a constraint to avoid duplicate values, so what's the problem here; for me still unclear?

[#5]

@olafhelper , in the create code I added in the original message, you can see that the primary key is a compound key made up from the person_id and phone_luid fields. So there cannot be a duplicate combination of these combined.

The phone_luid for the Mobile Phone type is 282. So there can only be one Mobile Phone listed for a give individual, but the actual phone number can be assigned to multiple types. This means that the Mobile Phone number, 555-555-5555, can have type 282 once, but it can also have type 261 once, and type 296 once...and other people can also have that same Mobile phone number listed on their record. This is not the nature of a Mobile Phone. A Mobile Phone belongs to one person and should not be associated with others. This is how Microsoft defined the Mobile Phone field in Dataverse. So, I need to create a constraint in the current SQL environment that prevents the Mobile Phone field from having more than one phone_luid and belonging to more than one person.

A Constraint can be added to a SQL table through a key, and index, or through the CONSTRAINT command. What I'm looking for help with is the complexity of the command to achieve what I've described. So far this is what I've determined, but this does not prevent the phone number from having only 1 person_id:

CREATE UNIQUE NONCLUSTERED INDEX IX_MobilePhone_PhoneNumber_PhoneType
	ON dbo.core_person_phone( phone_number_stripped ASC, phone_luid ASC )
	WHERE( phone_luid = 282 );