Duplicate key error when inserting into a table that has no records!!! Please help!

  • All,

    I'm having trouble with an insert statement that is throwing a duplicate error (shown below) on a table with zero records in it. I've attached the SQL insert statement and the table schemas in the attached text doc.

    Msg 2627, Level 14, State 1, Line 1

    Violation of PRIMARY KEY constraint 'PK_VimasProFast1'. Cannot insert duplicate key in object 'dbo.VimasProFast1'.

    The statement has been terminated.

    Let me know if anyone has any questions.

    Thanks in advance! 🙂

    David

  • You forgot to attach the doc 😛

  • The data you are inserting has duplicates in it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • In the select you have a duplicate key.

    Or you forgot to activate identity on your pk.

  • Yikes! Sorry, I forgot to attached the doc. Thanks for the heads up!!!

  • The problem is with the following fields: mersum.subisonum and mersum.repnum.

    You're grouping on the actual values (including NULLs), so you could potentially have a row with NULL and another with 0 for either of these values. When you insert these records, you're replacing the NULLs with 0, but you're not combining these rows, so you still have two rows, but now they have the same values.

    You either need to use IsNull in both the SELECT and GROUP BY clauses or the actual fields in both places.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • There is no security table in your sample script :ermm:

    No matter though. Here is your insert reformatted:

    -- insert statement

    INSERT INTO VimasProFast1

    SELECT MONTH(dbo.MerInfo.addtosys) AS month,

    YEAR(dbo.MerInfo.addtosys) AS year,

    mersum.isonum,

    ISNULL(mersum.subisonum, 0),

    ISNULL(mersum.repnum, 0) AS repnum,

    COUNT(mersum.status) AS total,

    NEWID()

    FROM dbo.MerSum (NOLOCK)

    INNER JOIN dbo.MerInfo (NOLOCK) ON dbo.MerSum.Intmid = dbo.MerInfo.Intmid

    WHERE dbo.MerSum.isonum IN (SELECT DISTINCT

    userid

    FROM security

    WHERE username LIKE 'iso%'

    AND lastaccess > (GETDATE() - 30))

    AND dbo.MerInfo.addtosys >= DATEADD(mm, -12, GETDATE())

    AND dbo.MerInfo.addtosys <= GETDATE()

    AND mersum.status <> 0

    GROUP BY MONTH(dbo.MerInfo.addtosys),

    YEAR(dbo.MerInfo.addtosys),

    mersum.isonum,

    mersum.subisonum,

    mersum.repnum

    The issue, as others have alluded to, is that according to the PK on VimasProFast1 your query result contains duplicates.

    The PK on VimasProFast1 is:

    [month] ASC, [year] ASC, [isonum] ASC, [subisonum] ASC, [repnum] ASC

    which almost matches your GROUP BY. Note the bolded items in your query above. My guess is that one of the NULL values is being converted to a 0 thereby creating a duplicate in your result set.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • I think I see your problem. In your SELECT clause, you say "ISNULL(mersum.subisonum,0),isnull(mersum.repnum,0) as repnum". In your GROUP BY clause, you say "mersum.subisonum,mersum.repnum".

    If you change the GROUP BY clause to be: "ISNULL(mersum.subisonum, 0), ISNULL(mersum.repnum, 0)" it should work.

    Take this as a sample:

    CREATE TABLE #TEST

    (

    ID INT NOT NULL,

    ID2 INT

    )

    INSERT INTO #Test (ID, ID2)

    VALUES (1, 0)

    INSERT INTO #Test (ID, ID2)

    VALUES (1, NULL)

    SELECT ID, ISNULL(ID2, 0)

    FROM #Test

    GROUP BY ID, ID2

    SELECT ID, ISNULL(ID2, 0)

    FROM #Test

    GROUP BY ID, ISNULL(ID2, 0)

    In the first select, two rows are returned, in the second, one row is returned.

    BTW - I would recommend against having your primary key be across five columns like that. But that's just me.

  • All,

    I must doing something wrong as all the examples that were posted are still giving me a duplicate error.

    Any ideas?

    Thanks!

  • Please post the new query you're trying.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • -- insert statement

    INSERT INTO VimasProFast1

    SELECT MONTH(dbo.MerInfo.addtosys) AS month,

    YEAR(dbo.MerInfo.addtosys) AS year,

    mersum.isonum,

    ISNULL(mersum.subisonum, 0),

    ISNULL(mersum.repnum, 0) AS repnum,

    COUNT(mersum.status) AS total,

    NEWID()

    FROM dbo.MerSum (NOLOCK)

    INNER JOIN dbo.MerInfo (NOLOCK) ON dbo.MerSum.Intmid = dbo.MerInfo.Intmid

    WHERE dbo.MerSum.isonum IN (SELECT DISTINCT

    userid

    FROM security

    WHERE username LIKE 'iso%'

    AND lastaccess > (GETDATE() - 30))

    AND dbo.MerInfo.addtosys >= DATEADD(mm, -12, GETDATE())

    AND dbo.MerInfo.addtosys <= GETDATE()

    AND mersum.status <> 0

    GROUP BY MONTH(dbo.MerInfo.addtosys),

    YEAR(dbo.MerInfo.addtosys),

    mersum.isonum,

    mersum.subisonum,

    mersum.repnum

  • davidsalazar01 (7/6/2011)


    -- insert statement

    INSERT INTO VimasProFast1

    SELECT MONTH(dbo.MerInfo.addtosys) AS month,

    YEAR(dbo.MerInfo.addtosys) AS year,

    mersum.isonum,

    ISNULL(mersum.subisonum, 0),

    ISNULL(mersum.repnum, 0) AS repnum,

    COUNT(mersum.status) AS total,

    NEWID()

    FROM dbo.MerSum (NOLOCK)

    INNER JOIN dbo.MerInfo (NOLOCK) ON dbo.MerSum.Intmid = dbo.MerInfo.Intmid

    WHERE dbo.MerSum.isonum IN (SELECT DISTINCT

    userid

    FROM security

    WHERE username LIKE 'iso%'

    AND lastaccess > (GETDATE() - 30))

    AND dbo.MerInfo.addtosys >= DATEADD(mm, -12, GETDATE())

    AND dbo.MerInfo.addtosys <= GETDATE()

    AND mersum.status <> 0

    GROUP BY MONTH(dbo.MerInfo.addtosys),

    YEAR(dbo.MerInfo.addtosys),

    mersum.isonum,

    mersum.subisonum,

    mersum.repnum

    Looks the same as before :ermm: Not sure what else can be said about it. The two pairs of bolded lines above need to be the same in the SELECT column list and the GROUP BY. Multiple previous posts point out the same thing just worded differently.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • All,

    After I changed the query to the one below and received the following error:

    Msg 515, Level 16, State 2, Line 1

    Cannot insert the value NULL into column 'subisonum', table 'Vimas.dbo.VimasProFast1'; column does not allow nulls. INSERT fails.

    The statement has been terminated.

    INSERT INTO VimasProFast1

    SELECT MONTH(dbo.MerInfo.addtosys) AS month,

    YEAR(dbo.MerInfo.addtosys) AS year,

    mersum.isonum,

    mersum.subisonum,

    mersum.repnum,

    COUNT(mersum.status) AS total,

    NEWID()

    FROM dbo.MerSum (NOLOCK)

    INNER JOIN dbo.MerInfo (NOLOCK) ON dbo.MerSum.Intmid = dbo.MerInfo.Intmid

    WHERE dbo.MerSum.isonum IN (SELECT DISTINCT

    userid

    FROM security

    WHERE username LIKE 'iso%'

    AND lastaccess > (GETDATE() - 30))

    AND dbo.MerInfo.addtosys >= DATEADD(mm, -12, GETDATE())

    AND dbo.MerInfo.addtosys <= GETDATE()

    AND mersum.status <> 0

    GROUP BY MONTH(dbo.MerInfo.addtosys),

    YEAR(dbo.MerInfo.addtosys),

    mersum.isonum,

    mersum.subisonum,

    mersum.repnum

  • Try running just the Select part of the query and look for NULLs in the subisonum column.

    That should show you which records are causing the issue.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • I think you went in the wrong direction...you want to bring these into the GROUP BY:

    ISNULL(mersum.subisonum, 0),

    ISNULL(mersum.repnum, 0)

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply