September 8, 2013 at 2:25 pm
Hi All,
Please I need help on how to solve this problem. I have table variable in which I am inserting data from sql server database. I have made one of the columns called repaidID a primary key so that a clustered index will be created on the table variable. When I run the stored procedure used to insert the data. I have this error message; Violation of Primary key Constraint. Cannot insert duplicate primary key in object. The value that is causing this error is (128503).
I have queried the repaidid 128503 in the database to see if it is a duplicate but could not find any duplicate. The repaidID is a unique id normally use by my company and does not have duplicates. Please any help will be appreciated.
EO
September 8, 2013 at 4:04 pm
When you get this error, this either because the value is a duplicate in the source data, or because your query produces duplicate rows because of an incorrect join condition or similar. Since you say that the source value is unique, it appears that it is the latter option that applies.
If you want help to understand what is wrong with your query, you need to post it.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 8, 2013 at 4:31 pm
eobiki10 (9/8/2013)
Hi All,I have this error message; Violation of Primary key Constraint. Cannot insert duplicate primary key in object. The value that is causing this error is (128503).
I have queried the repaidid 128503 in the database to see if it is a duplicate but could not find any duplicate.
EO
Of course you will not find a duplicate key (128503) in the database because of the primary key.
Hint:
Create a temp table with the same structure as the one you're inserting in and without primary key. Change the SP to make insert in that temp table. Then you'll be able to find (or ensure) any duplicate keys and analyze your code.
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
September 10, 2013 at 2:36 pm
Hi ,
This is the code that I have. I really want to know which one is inserting the duplicate that violates the primary key constraints.
Thanks
DECLARE @History Table
(
Jobnumber INT
,HouseReference NVARCHAR(20)
,AddressLine1nvarchar (30)
,Postcodenvarchar (8)
,Prioritynvarchar (20)
,JobTypenvarchar (20)
,Contractornvarchar (30)
,Officename nvarchar (20)
,ValuePaid MONEY
,HistDate DATE
, PRIMARY KEY (Jobnumber)
)
INSERT INTO @History
SELECT r.jobno
, r.proref
, p.proadd1
, p.propstcde
, pr.decode
, re.code
, wo.wfeedname
, m.mnodename
, wn.payval
, s.statedate
FROM repository r
INNER JOIN work w ON r.jobno = w.jobno
LEFT JOIN repaid re ON re.code = r.code
LEFT JOIN property p ON r.proref = p.proref
LEFT JOIN priority pr ON pr.code = w.prioritycode
LEFT JOIN statement s ON s.jobno = r.jobno
LEFT JOIN mnoded m ON p.mnodedcode = m.code
LEFT JOIN workfeed wo ON r.wfeedcode = wo.wfeedcode
LEFT JOIN wonderlife wn ON w.jobno = wn.jobno
ORDER BY r.jobno
September 10, 2013 at 2:39 pm
if you're not writing directly to the final table, you could use a SELECT DISTINCT instead of a plain SELECT and it would remove duplicates for you. It's more expensive in terms of processing, but it would at least work.
September 10, 2013 at 2:50 pm
Hi,
I have tried the SELECT distinct but it didnt work. As Erland Sommarskog said in the first reply that it has to do with the Joins so I am kind of lost on how to go about it.
EO
September 10, 2013 at 3:43 pm
Let me first commend you for adding that primary key. That constraint served as an assertion of your assumption about the data. Your assumption proved to be incorrect, but thanks to the assertion, the error was caught early rather than producing incorrect results.
Now over the query. We have this FROM clause:
FROM repository r
INNER JOIN work w ON r.jobno = w.jobno
LEFT JOIN repaid re ON re.code = r.code
LEFT JOIN property p ON r.proref = p.proref
LEFT JOIN priority pr ON pr.code = w.prioritycode
LEFT JOIN statement s ON s.jobno = r.jobno
LEFT JOIN mnoded m ON p.mnodedcode = m.code
LEFT JOIN workfeed wo ON r.wfeedcode = wo.wfeedcode
LEFT JOIN wonderlife wn ON w.jobno = wn.jobno
Now, I don't know anything about these tables. But I assume that when the join is something else than jobno, the table is a lookup code of some sort. That is, in the tables repaid, property, priority, mnoded and workfeed, the primary keys are code, proref, code, code and wfeedcode respectively. Under this assumption, each such join hits 0 or 1 rows.
On the other hand, these two:
LEFT JOIN statement s ON s.jobno = r.jobno
LEFT JOIN wonderlife wn ON w.jobno = wn.jobno
are likely to cause you trouble. When I see this, I assume that there can be 0 to many rows for the same jobno in both statement and wonderlife. This means that you can get multiple rows in the output for the same jobno. Furthermore, if there are 3 matching rows in statement and 5 rows in wonderlife, you will get 15 rows in total, because these rows will multiply with each other.
How you should fix it? You need to review your business requirements? What are you trying to achieve? And of course you need to review the cardinality of all dependencies in the query. The ones on statement and wonderlife are the most likely culprits, but you should review the others as well, just to make sure.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 10, 2013 at 4:10 pm
Thanks Erland Sommarskog
I will check the code again with your suggestions in the office tomorrow.
EO
July 2, 2015 at 5:08 pm
This is what I'm receiving on a website I have had saved for months now and only if I understood all this computer talk....
July 2, 2015 at 11:03 pm
The steps I take to debug such a problem are simply isolate it and break it down.
Pull your query out , put a INTO #temp (and get rid of the Order by)
Then run something like:
Select jobnumber, count(1) From #temp Group by jobnumber having count(1) > 1
This will at least tell you which jobnumbers are screwing you up. If you needed to see the full line for each then use something like:
Select A.* from #Temp A
inner join (Select jobnumber, count(1) From #temp Group by jobnumber having count(1) > 1) B
ON A.jobnumber = B.jobnumber
pretty crude, but it is fast to write, removes some complication, then by trial and error you can drop joins until you don't get duplication, then you know what joins caused the problem.
Just a pointer, Distinct doesn't work in your case because something else on the duplicate jobnumber records is not exactly the same.
July 3, 2015 at 5:14 am
This should find you the rows in the result set that have the same jobno:
;with temp as (SELECT r.jobno
, r.proref
, p.proadd1
, p.propstcde
, pr.decode
, re.code
, wo.wfeedname
, m.mnodename
, wn.payval
, s.statedate
FROM repository r
INNER JOIN work w ON r.jobno = w.jobno
LEFT JOIN repaid re ON re.code = r.code
LEFT JOIN property p ON r.proref = p.proref
LEFT JOIN priority pr ON pr.code = w.prioritycode
LEFT JOIN statement s ON s.jobno = r.jobno
LEFT JOIN mnoded m ON p.mnodedcode = m.code
LEFT JOIN workfeed wo ON r.wfeedcode = wo.wfeedcode
LEFT JOIN wonderlife wn ON w.jobno = wn.jobno),
dupes AS (SELECT jobno
FROM temp
group by jobno
having count(*) > 1)
select t.*
fromtempt
joindupesd on d.jobno = d.jobno
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply