Violation of PRIMARY KEY constraint

Question

Post reply

Violation of PRIMARY KEY constraint

martin.kerr 34088

SSCommitted

Points: 1752
More actions
November 2, 2011 at 5:57 am

#246169

Hi all,
i am trying to execute this query:
begin tran
insert into Attributes (Id, EntityId, AttributeName, IgnoreOriginal,
OriginalValue, NewValue)
select Id, EntityId, AttributeName, IgnoreOriginal,
OriginalValue, NewValue
from MOHG_ProcessedData_01112011.dbo.Attributes
but am getting this error:
Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK_Attributes'. Cannot insert duplicate key in object 'dbo.Attributes'. The duplicate key value is (1129614).
any help is appreciated

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

you have a primary key and are trying to insert a duplicate.

search your source table for id =1129614 and it should have more than one row.

martin.kerr 34088 SSCommitted Points: 1752 More actions · Answer 2

yes this is correct; so how do i make the id sequential starting from 1000000? so i do not have any duplicates

martin.kerr 34088 SSCommitted Points: 1752 More actions · Answer 3

i used this code to do this:

DECLARE @SourceID INT;
DECLARE @Counter INT;
SET @Counter = 1;
DECLARE ins_accounts CURSOR
FOR
SELECT id FROM attributes
OPEN ins_accounts
FETCH NEXT FROM ins_accounts
INTO @SourceId
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE attributes
SET id = 1000000 + CAST(@Counter AS int)
WHERE id = @SourceID
FETCH NEXT FROM ins_accounts INTO @SourceId
SET @Counter = @Counter+1
END
CLOSE ins_accounts
DEALLOCATE ins_accounts

Lowell SSC Guru Points: 323518 More actions · Answer 4

since you are migrating from another table, and at least one value already exists, shouldn't you be joiing on the local data to prevent the duplicates from being inserted?

or do you really want to re-insert the data but with new ID's? is the table 's [ID] column an identity?

something like this?

INSERT INTO Attributes

(Id,

EntityId,

AttributeName,

IgnoreOriginal,

OriginalValue,

NewValue)

SELECT RemoteSource.Id,

RemoteSource.EntityId,

RemoteSource.AttributeName,

RemoteSource.IgnoreOriginal,

RemoteSource.OriginalValue,

RemoteSource.NewValue

FROM MOHG_ProcessedData_01112011.dbo.Attributes RemoteSource

LEFT OUTER JOIN Attributes LocalSource

ON RemoteSource.ID = LocalSource.ID

WHERE LocalSource.ID IS NULL --not yet migrated.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

martin.kerr 34088 SSCommitted Points: 1752 More actions · Answer 5

please see my posts above, i do not want duplicates but need to set the id sequential starting from 1000000

steveb. SSC-Forever Points: 46733 More actions · Answer 6

so the id is meaningless?

if so you can use

DBCC CHECKIDENT ("Attributes ", RESEED, 10000);

to reseed you id column

martin.kerr 34088 SSCommitted Points: 1752 More actions · Answer 7

yes pretty much meaningless, what does that statement actually do though?

'id' does mean something but i have 2 tables with results in that i want to amalgamate but they both have the same sequential 'id'

steveb. SSC-Forever Points: 46733 More actions · Answer 8

steveb.

SSC-Forever

Points: 46733

November 2, 2011 at 6:40 am

#1402108

it starts the id at 10000

sami.sqldba Ten Centuries Points: 1248 More actions · Answer 9

Hi

Try this it will make you insert from other table and id starting with 1000000

DECLARE @START INT

DECLARE @END INT

SELECT @START = 1000000 --START VALUE

SET @END = (SELECT COUNT(*) FROM Attributes)

SELECT @END + 1000000 --END VALUE

WHILE @START <= @END --CONDITION WHERE STARTVALUE SHOULD BE LESS THEN END VALUE

BEGIN

insert into Attributes (Id, EntityId, AttributeName, IgnoreOriginal,

OriginalValue, NewValue)

select @START, EntityId, AttributeName, IgnoreOriginal,OriginalValue, NewValue

from MOHG_ProcessedData_01112011.dbo.Attributes

SET @START = @START + 1 --INCREMENTING THE START VALUE

END

Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 10

sami.sqldba (1/17/2012)
Hi
Try this it will make you insert from other table and id starting with 1000000
DECLARE @START INT
DECLARE @END INT
SELECT @START = 1000000 --START VALUE
SET @END = (SELECT COUNT(*) FROM Attributes)
SELECT @END + 1000000 --END VALUE
WHILE @START <= @END --CONDITION WHERE STARTVALUE SHOULD BE LESS THEN END VALUE
BEGIN
insert into Attributes (Id, EntityId, AttributeName, IgnoreOriginal,
OriginalValue, NewValue)
select @START, EntityId, AttributeName, IgnoreOriginal,OriginalValue, NewValue
from MOHG_ProcessedData_01112011.dbo.Attributes
SET @START = @START + 1 --INCREMENTING THE START VALUE
END

This will always fail, since every row to be inserted into the attributes table (in the only insert to run) will have the same Id: 1000000

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 11

martin.kerr 34088 (11/2/2011)
i used this code to do this:
...
UPDATE attributes
SET id = 1000000 + CAST(@Counter AS int)
WHERE id = @SourceID
FETCH NEXT FROM ins_accounts INTO @SourceId
SET @Counter = @Counter+1
...

Looks like you are just resequencing the ids, if so, try it like this:

UPDATE Identifiers

SET Id = Sequence

FROM

(

SELECT

Id,

Sequence =

1000000 +

ROW_NUMBER() OVER (

ORDER BY Id)

FROM MOHG_ProcessedData_01112011.dbo.Attributes

) AS Identfiers;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi