Primary Key Violation

Hi There

I am trying to insert records in a table from other server via DTS package.
The new table is empty.I am using query to import records in the table. The query use the distinct keywork in it even then dts is failing with error that primary key violaction.
How can it be possible that i am violating the primary key when all the records as distinct.

Thanks

Satya, Thanks for support. Can you please explain it more.. so that i can get the clue where to start working to resolve this issue.

Thanks alot
Danny

This is the table design if it can help some how.

/****** Object: Table [dbo].[tblLoadSTCustomers] Script Date: 5/23/2007 10:08:48 AM ******/
CREATE TABLE [dbo].[tblLoadSTCustomers] (
[nmbrSourceCustomerID] [int] NOT NULL ,
[strEmail] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[strFName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[strLName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[strFullName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[strOrganization] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dtmBirthDate] [datetime] NULL ,
[strSource] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dtmDateAdded] [datetime] NULL ,
[dtmDateUpdated] [datetime] NULL ,
[nmbrAddedBy] [int] NOT NULL ,
[strUserName] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ysnCustomerExists] [bit] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblLoadSTCustomers] WITH NOCHECK ADD
CONSTRAINT [PK_tblLoadSTCustomers] PRIMARY KEY CLUSTERED
(
[nmbrSourceCustomerID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblLoadSTCustomers] WITH NOCHECK ADD
CONSTRAINT [DF_tblLoadSTCustomers_ysnAlreadyExists] DEFAULT (0) FOR [ysnCustomerExists]
GO

CREATE INDEX [IX_tblLoadSTCustomers_Email] ON [dbo].[tblLoadSTCustomers]([strEmail]) WITH FILLFACTOR = 80, PAD_INDEX ON [PRIMARY]
GO

Thanks
Danny

BEGIN

DECLARE @dtmImportDateStart datetime
DECLARE @dtmImportDateStop datetime
DECLARE @nmbrMaxPersonalID int
SELECT @nmbrMaxPersonalID = max(nmbrMaxPersonalID), @dtmImportDateStart = MAX(dtmImportDateStart), @dtmImportDateStop = MAX(dtmImportDateStop) FROM tblStreetTeamImportLog

Select distinctnmbrSourceCustomerID = UUL.nmbrLastPersonalID--LG.personalid
, strEmail = UUL.strEmail--LTRIM(RTRIM(LG.Email))
, strFName = LTRIM(RTRIM(MI.FirstName))
, strLName = LTRIM(RTRIM(MI.LastName))
, strFullName = LTRIM(RTRIM(MI.FirstName)) + ' ' + LTRIM(RTRIM(MI.LastName))
, strOrganization = Case When LTRIM(RTRIM(MI.Organization)) = '' Then Null
Else LTRIM(RTRIM(MI.Organization))
End
, dtmBirthDate = MI.BirthDate
, strSource = 'PETA2 STREET TEAMS'
, dtmDateAdded = UUL.dtmDateAdded
, dtmDateUpdated = UUL.dtmDateUpdated
, nmbrAddedBy = 0-- This is needed.. does not allow nulls

, strUserName = UUL.strEmail--LTRIM(RTRIM(LG.FullName))-- This is as close as I can find to a user name...

-- Select Count(Distinct LG.personalid)
From (Select Distinct nmbrLastPersonalID = Max(LG.personalid)
, strEmail = LTRIM(RTRIM(LG.Email))
, dtmDateAdded = Max(MI.CreateDate)
, dtmDateUpdated = MAX(MI.Lastmodified)
From dbo.Login LG
Inner Join Team_Members TM On (LG.personalid = TM.personalid
And Coalesce(TM.actionID, 0) = 531)-- 531 = peta2
Left Join dbo.Member_Info MI On (LG.personalid = MI.personalid
And Coalesce(MI.actionID, 0) = 531)-- 531 = peta2
Where (Not LTRIM(RTRIM(Coalesce(LG.Email, ''))) = '')
Group By LTRIM(RTRIM(LG.Email))) UUL
Inner Join dbo.Login LG On (UUL.nmbrLastPersonalID = LG.personalid)
-- Get the info from here not from Names... there's more data in Member_Info as of 10/5
Left Join dbo.Member_Info MI On (UUL.nmbrLastPersonalID = MI.personalid
And Coalesce(MI.actionID, 0) = 531)-- 531 = peta2
-- Retrieve the last activity date
Left Join (Select LU.personalid, LastLogDT = Max(LU.tstamp)
From Log_usage LU
Group By LU.personalid) LASTLOG On (UUL.nmbrLastPersonalID = LASTLOG.personalid)
WHERE (lg.personalid > @nmbrMaxPersonalID
OR COALESCE(UUL.dtmDateUpdated, dtmDateAdded) BETWEEN @dtmImportDateStart AND @dtmImportDateStop)
ORDER BY nmbrSourceCustomerID asc
END

I love the way you're double-, triple- and quadruple-checking the uniqueness of that PersonalID - but you have to look at the other tables in that join.

There will be values of PersonalID that match with more than one row from the other table(s) in the query, where the values on the other columns contained in the resultset are different, so you are still getting duplicate PersonalID values on your resultset, despite of the DISTINCT keyword.

no there are arround 200 records in the result set and i have chacked them manually...there is no duplicate record in the resultset.

Thanks
Danny

No duplicate rows, correct - but among those rows, you will find at least one PersonalID (if not more), occurring on at least two rows (if not more).

The primary key of the target table is on PersonalID only, so you cannot insert different records with identical PersonalID's.

allright, now when i tried this there was no primary constraint violation.
I created a temp table and stored the result of the query in that temp table. Than i used dts to copy the records from that temp table to the desired table on the other server. And it gone through successfully.
Any clue?

Yeah i made one change while creating the temp table i used the same script which i just posted above. What it did it created the last column ysnCustomerExists also in the Temp table. now temp table have all the columns as the destination table.and i put the default value in that bit type colum as 0.
So it gone through fine.

Now even when i try to do the same thing with original DTS package and modify query by adding a extra field and assigning it value 0. It does not go through giving the same error.

But one thing is sure...this is something rel;ated to that ysnCustomerExist (bit ) type No null coumn.

Can some one please advice what else can i try.

Thanks
Danny

You could add MIN() or MAX() around the value you're inserting into that column, whichever is appropriate.

If one result with "1" is more important than any result with "0", then use MAX().
If one result with "0" is more important than any result with "1", then use MIN().

i just fixed the problem by adding a extra step in between where its getting records in the temp table and then in next step moing data between two tables. And now its working flawlessly.

But i still dont understand the problem which was casuing the earlier degisn to fail.

Thanks everybody for support!!
Danny

Copy the resultset on which you were getting the key violation error, into another table.

Then run a query on that other table like -

SELECT PersonalId, COUNT(*)
FROM other_table
GROUP BY PersonalId
HAVING COUNT(*) > 1

You should find a few ...

i seriously checked it manually...and with the group by query also... there is no duplicate record...i myself i am surprized what the issue was?
but still i will try your method and let you know if i find any thing suspisious

Thanks
Danny

Remote possibility ...

The key violation message should also tell you which constraint is violated, and it may be a constraint on a different table than the target table. So check the exact message.

If the target table has an insert triggers, this trigger might in turn insert rows into another table, which in some way might cause a key violation.

Yeah this can be the reason , i checked it from the Query analyzer but there it shows no trigger on that table. If there is any other way of checking the trigger on a table please let me know.

Thanks alot for support!!
Danny

Does the error message mention the "PK_tblLoadSTCustomers" constraint?

yes, It mention exactly this error...

Any clue???

Nope.

Try this one to double-check - this returns the number of rows, and the number of distinct PersonalId values - if the second count is lower than the first count, then you have duplicates:

DECLARE @dtmImportDateStart datetime
DECLARE @dtmImportDateStop datetime
DECLARE @nmbrMaxPersonalID int

SELECT @nmbrMaxPersonalID = max(nmbrMaxPersonalID), @dtmImportDateStart = MAX(dtmImportDateStart), @dtmImportDateStop = MAX(dtmImportDateStop) FROM tblStreetTeamImportLog

SELECT COUNT(*), COUNT(DISTINCT nmbrSourceCustomerID)
FROM
(
Select distinct nmbrSourceCustomerID = UUL.nmbrLastPersonalID --LG.personalid
, strEmail = UUL.strEmail --LTRIM(RTRIM(LG.Email))
, strFName = LTRIM(RTRIM(MI.FirstName))
, strLName = LTRIM(RTRIM(MI.LastName))
, strFullName = LTRIM(RTRIM(MI.FirstName)) + ' ' + LTRIM(RTRIM(MI.LastName))
, strOrganization = Case When LTRIM(RTRIM(MI.Organization)) = '' Then Null
Else LTRIM(RTRIM(MI.Organization))
End
, dtmBirthDate = MI.BirthDate
, strSource = 'PETA2 STREET TEAMS'
, dtmDateAdded = UUL.dtmDateAdded
, dtmDateUpdated = UUL.dtmDateUpdated
, nmbrAddedBy = 0 -- This is needed.. does not allow nulls
, strUserName = UUL.strEmail
From (Select Distinct nmbrLastPersonalID = Max(LG.personalid)
, strEmail = LTRIM(RTRIM(LG.Email))
, dtmDateAdded = Max(MI.CreateDate)
, dtmDateUpdated = MAX(MI.Lastmodified)
From dbo.Login LG
Inner Join Team_Members TM On (LG.personalid = TM.personalid
And Coalesce(TM.actionID, 0) = 531) -- 531 = peta2
Left Join dbo.Member_Info MI On (LG.personalid = MI.personalid
And Coalesce(MI.actionID, 0) = 531) -- 531 = peta2
Where (Not LTRIM(RTRIM(Coalesce(LG.Email, ''))) = '')
Group By LTRIM(RTRIM(LG.Email))) UUL
Inner Join dbo.Login LG On (UUL.nmbrLastPersonalID = LG.personalid)
-- Get the info from here not from Names... there's more data in Member_Info as of 10/5
Left Join dbo.Member_Info MI On (UUL.nmbrLastPersonalID = MI.personalid
And Coalesce(MI.actionID, 0) = 531) -- 531 = peta2
-- Retrieve the last activity date
Left Join (Select LU.personalid, LastLogDT = Max(LU.tstamp)
From Log_usage LU
Group By LU.personalid) LASTLOG On (UUL.nmbrLastPersonalID = LASTLOG.personalid)
WHERE (lg.personalid > @nmbrMaxPersonalID
OR COALESCE(UUL.dtmDateUpdated, dtmDateAdded) BETWEEN @dtmImportDateStart AND @dtmImportDateStop)
ORDER BY nmbrSourceCustomerID asc)
AS x

It gives same...232 = 232[<img src='/community/emoticons/emotion-6.gif' alt='' />]<br /><br />But the second method is running fine i mean getting the results in the temp table and then using that temp for data transfer.<br />Not sure what is the reason with first method.<br /><br />Thanks alot <br />Danny

Try unknotting the spaghetti ...

Start with a query that gives you just the distinct list of relevant PersonalId's. Use this as a derived table, so its complexity does not obfuscate the other parts of the query.

Then join the derived table to the other tables one at a time, adding the details. If you need to aggregate, start a separate query to do this with just the base table for the PersonalId, and after this works, you replace the base table with the derived table again.

And so on, and so forth ...

yeah. we have the same message too. there are not any duplicate records from source table. we use DTS package to copy data from one server to another server. i checked the records one by one. nothing duplate. we are not able to find out what is wrong in DTS. always the data is always copied to the destination table even though we get this "Violation of PRIMARY KEY constraint xxxxxxx."

Uh, you're inserting data into a table - if that table already contains the same key value, then you get a key violation.<br /><br />If you want to "upsert", step 1 is to update existing matches:<br /><br />UPDATE tgt<br />SET tgt.col1 = src.col1, tgt.col2 = src.col2 &lt;etc.&gt;<br />FROM target_table tgt<br />INNER JOIN (&lt;derived_table&gt<img src='/community/emoticons/emotion-5.gif' alt='' /> src<br />ON tgt.PersonalId = src.PersonalId<br /><br />... and step 2 is to insert new rows:<br /><br />INSERT INTO tgt (...)<br />SELECT src.*<br />FROM (&lt;derived_table&gt<img src='/community/emoticons/emotion-5.gif' alt='' /> src<br />LEFT JOIN target_table tgt<br />ON tgt.PersonalId = src.PersonalId<br />WHERE tgt.PersonalId IS NULL<br /><br />Always do the update before the insert.

In my case data was not even getting copied. DTS was simply failing with the error as stated.And i have checked each record manually, with query on both side there is no duplicates in the result set to be transfered to target table.And when i copy the result set in to temp table and then copying them into target table form the temp table , it works fine.
I mean i dont see any logical error in the whole process.
Are you sure its not a bug?

Thanks
Danny

I have seen similar type of issue...it was the query which was generating the dup results...

In your case did you try using the exption file in dts to see which is the culprit.
Try using exemption file and put the commit batch to one row so that you can find the problem...


MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.