having problem inserting records into table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

having problem inserting records into table

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-10-03 : 21:03:15

Hi guys,

May i know the issue why i'm getting this error when inserting records to table. even i removed already the duplicate records still got this error. total records to be inserted is 37,000, when i try to dump the first 1000 records it was successfull.

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__Inventta__EAE40AAB0F975522'. Cannot insert duplicate key in object 'dbo.mytable.
The statement has been terminated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-10-03 : 21:06:49

basically it means, you still have duplicate primary key in your data

this should get rid of the error


select *
from
(
    select *, rn = row_number() over (partition by pk_col order by somecol)
    from   yoursource
) d
where d.rn = 1

but you should investigate why your data has duplicate PK.

To view those duplicate data, just change the above query to d.rn > 1

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-04 : 01:34:44

even i removed already the duplicate records still got this error.

it may be that the current select tself is bringing duplicate from the source. Check on basis of key to find any duplicate occurances


SELECT Col1,Col2,...
FROM Source
GROUP BY Col1, Col2,..
HAVING COUNT(*) >1

where Col1,Col2,... forms the key combination

once this is identified you need to arrive at a logic to return only one out of the duplicate values in each group based on your business case or redefine your key column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources