SQL Server Performance

Primary Key Violation

Discussion in 'General DBA Questions' started by danny123, May 23, 2007.

  1. danny123 New Member

    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
  2. satya Moderator

    The issue is actually that you have defined that one or more columns are unique, but the data you are importing into it is NOT unique. SO its not a dTS issues this is a table related issue.


    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. danny123 New Member

    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
  4. danny123 New Member

    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
  5. FrankKalis Moderator

    The table design is not so important. More important is what happens inside the package. The query for example.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  6. danny123 New Member

    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
  7. Adriaan New Member

    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.
  8. danny123 New Member

    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
  9. Adriaan New Member

    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.
  10. danny123 New Member

    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
  11. Adriaan New Member

    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().
  12. danny123 New Member

    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
  13. Adriaan New Member

    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 ...
  14. danny123 New Member

    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
  15. Adriaan New Member

    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.
  16. danny123 New Member

    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
  17. Adriaan New Member

    Does the error message mention the "PK_tblLoadSTCustomers" constraint?
  18. danny123 New Member

    yes, It mention exactly this error...

  19. danny123 New Member

    Any clue???
  20. satya Moderator

    Look at that constraint for the relevant tables reference or SP_HELP tablname on the involved tables here.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  21. Adriaan New Member

    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
  22. danny123 New Member

    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
  23. Adriaan New Member

    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 ...
  24. xuka200308 New Member

    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."



  25. Adriaan New Member

    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.
  26. danny123 New Member

    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
  27. MohammedU New Member

    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.

Share This Page