CODEWITHSUNDEEP
sqlsql-server
enigma
enigma

Reputation: 82

Cannot insert duplicate key SQL

insert into A (id,Name)
select ti.id,ti .Name 
from A ti 
where ti.id >= 1 AND ti.id<=3

id is the primary key but not autogenerated. When I run the query I get an error

Violation of PRIMARY KEY constraint 'XPKA'. Cannot insert duplicate key in object 'dbo.A'

tabel A

id Name
1   A
2   B
3   C

and I want to insert 

id Name
4   A
5   B
6   C

Upvotes: 3

Views: 61391

Answers (4)

Dan Metheus
Dan Metheus

Reputation: 1438

You need to adjust the ID of the rows you are inserting. In your example to produce keys 4, 5, 6:

insert into A (id,Name)
select ti.id + 3 as NewKey,ti.Name 
from A ti 
where ti.id >= 1 AND ti.id<=3

But in reality you need to pick a value that will keep your new keys separate from any possible old key, maybe:

insert into A (id,Name)
select ti.id + 100000 as NewKey,ti.Name 
from A ti 
where ti.id >= 1 AND ti.id<=3

Upvotes: 1

Zeddy
Zeddy

Reputation: 2089

As Yaakov Ellis has said...

Every row must have a different value for the Primary Key column.

And as you have a WHERE clause which constricts your rows to 3 in total EVER

Those with the unique Id's 1, 2 and 3

So if you want to replace those rather then tring to INSERT them where they already exist and generating your error.

Maybe you could UPDATE them instead? And that will resolve your issue.

UPDATE

After your addition of extra code... You should set your UNIQUE Key Identifier to the ID Number and not the ABC field name (whatever you have called it)

Upvotes: 0

Jon Egerton
Jon Egerton

Reputation: 41549

You are selecting from table A and inserting straight back in to it. This means that the ID values you insert will certainly already be there.

The message says that ID col has a PrimaryKey on it and requires the values in the column to be unique. It won't let you perform the action for this reason.

To fix your query based on your stated requirement, change the script to:

insert into A (id,Name)
select ti.id + 3,ti .Name 
from A ti 
where ti.id >= 1 AND ti.id<=3

Upvotes: 3

Yaakov Ellis
Yaakov Ellis

Reputation: 41490

Every row must have a different value for the Primary Key column. You are inserting the records from A back into itself, thus you are attempting to create a new row using a Primary Key value that is already being used. This leads to the error message that you see.

If you must insert records in this fashion, then you need to include a strategy for including unique values in the PK Column. If you cannot use an autoincrement rule (the normal method), then your logic needs to enforce this requirement, otherwise you will continue to see errors like this.

Upvotes: 5

Related Questions