Violation of PRIMARY KEY constraint

[Dabase]

Hi,

I am trying to INSERT data to MyTable using a SELECT statement from another table, I am getting the following error:

Server: Msg 2627, Level 14, State 1, Line 4
Violation of PRIMARY KEY constraint 'PK_MyTable'. Cannot insert duplicate key in object 'MyTable'.
The statement has been terminated.

Is there a way of identifying the duplicate records at the source table?



Thanks
Dabase

 

[SQLDenis]

To test if the other table has dups run this
change field1 and field 2 with your fields
select
[field1],
[field2
from anothertable
group by [field1],
[field2
having count(*) > 1

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Yup. All possibilities:

a) source table has one dupe, target table has another one
b) source table has all dupes, target table has no dupes
c) a) + b)

You can check a) with INNER JOIN query, and b) with classic GROUP BY/HAVING COUNT(*) > 1 query.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)