July 6, 2011 at 11:06 am
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
July 6, 2011 at 11:17 am
You forgot to attach the doc 😛
July 6, 2011 at 11:32 am
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
July 6, 2011 at 11:37 am
In the select you have a duplicate key.
Or you forgot to activate identity on your pk.
July 6, 2011 at 12:02 pm
Yikes! Sorry, I forgot to attached the doc. Thanks for the heads up!!!
July 6, 2011 at 12:23 pm
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
July 6, 2011 at 12:23 pm
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
July 6, 2011 at 12:24 pm
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.
July 6, 2011 at 3:31 pm
All,
I must doing something wrong as all the examples that were posted are still giving me a duplicate error.
Any ideas?
Thanks!
July 6, 2011 at 3:35 pm
Please post the new query you're trying.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 6, 2011 at 3:41 pm
-- 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
July 6, 2011 at 3:48 pm
davidsalazar01 (7/6/2011)
-- insert statementINSERT 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
July 8, 2011 at 9:30 am
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
July 8, 2011 at 9:39 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 8, 2011 at 9:46 am
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