April 5, 2009 at 9:46 am
I am stymied. I created a database with an Advertiser Table (Advertiser) and an Advertiser Phone Table (A_Phone). (I copied the create table statements below.) The key of the A _Phone record is the AdvertiserID Key, which is a field in the Advertiser record. I extracted the Advertiser records from CRM with no problem. When I tried to extract the telephone info, I got a primary key violation. I don’t get it. The advertiser ID alone would be non-unique since one advertiser can have several different types of phone numbers, but since the key consists of 2 fields and the combinations will be unique, shouldn’t that be OK? (In CRM, the phone numbers are all on the customer record, under telephone1, telephone2, etc., and I just translate that into the phone type. Even when I execute just this simple section of the code, I get the primary key violation.
INSERT INTO [DIGADV].[dbo].[A_Phone]
([AP_AdvertiserID_Key]
,[AP_PhoneType]
,[AP_PhoneNumber])
(
SELECT adv.A_AdvertiserID
,'Mobile'
,ab.[Telephone2]
FROM [MSCRM].[dbo].[AccountBase] ab
JOIN [DIGADV].[dbo].[Advertiser] adv
ON ab.[Name] COLLATE DATABASE_DEFAULT = adv.A_AdvertiserName COLLATE DATABASE_DEFAULT
)
GO
Here is the create statement for A_Phone:
CREATE TABLE [dbo].[A_Phone](
[AP_AdvertiserID_Key] [int] NOT NULL,
[AP_PhoneType] [nvarchar](10) NOT NULL,
[AP_PhoneNumber] [nvarchar](21) NULL,
CONSTRAINT [PK_A_Phone] PRIMARY KEY CLUSTERED
(
[AP_AdvertiserID_Key] ASC,
[AP_PhoneType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Here is the create statement for Advertiser:
CREATE TABLE [dbo].[Advertiser](
[A_AdvertiserName] [nvarchar](65) NOT NULL,
[A_AdvertiserID] [int] IDENTITY(1001,1) NOT NULL,
[A_CRM_ID] [nvarchar](36) NOT NULL,
[A_GP_ID] [nvarchar](15) NULL,
[A_AdvertiserEmail] [nvarchar](50) NULL,
CONSTRAINT [PK_Advertiser] PRIMARY KEY CLUSTERED
(
[A_AdvertiserName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
April 5, 2009 at 10:08 am
Your query returns duplicates !
SELECT adv.A_AdvertiserID
,'Mobile'
,ab.[Telephone2]
FROM [MSCRM].[dbo].[AccountBase] ab
JOIN [DIGADV].[dbo].[Advertiser] adv
ON ab.[Name] COLLATE DATABASE_DEFAULT = adv.A_AdvertiserName COLLATE DATABASE_DEFAULT
Solution ?
- Check PK on names ?? (In my home town we had 3 people baring my name a couple of years ago (my home town only has a population of 10.000))
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 5, 2009 at 10:54 am
you can use this to find the duplicating records, based on your SQL;
SELECT adv.A_AdvertiserID
,'Mobile'
,ab.[Telephone2]
FROM [MSCRM].[dbo].[AccountBase] ab
JOIN [DIGADV].[dbo].[Advertiser] adv
ON ab.[Name] COLLATE DATABASE_DEFAULT = adv.A_AdvertiserName COLLATE DATABASE_DEFAULT
GROUP BY adv.A_AdvertiserID,ab.[Telephone2]
HAVING COUNT(*) > 1
Lowell
April 5, 2009 at 11:22 am
AdvertiserName is the primary key on the Advertiser table.
April 5, 2009 at 11:34 am
Lowell,
Using your script, I found that some of the CRM telephone numbers were NULL. I forgot to check if there were values there. Thanks so much!
April 5, 2009 at 1:20 pm
there you go!
it's always the unexpected values in the data that trip me up too.
way to go!
Lowell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply